With Power BI, you can easily create measures that are tailored to your organization’s fiscal year. This ensures that you have accurate and up-to-date data for budgeting, forecasting, and decision-making purposes.
By incorporating fiscal year measures into your Power BI reports and dashboards, you can gain a deeper insight into your organization’s financial performance and make more informed business decisions.
The article provides a step-by-step guide to creating a measure that calculates the current and previous fiscal year using a fiscal calendar table in order to improve your financial reporting systems.
Let’s dive in!
Understanding Fiscal Periods
Before creating the measures, let us first explore what fiscal periods are and how they work. A fiscal period is simply a set period of time used for financial reporting purposes — which could be a quarter, a month, or even a week.
It is important to note that fiscal periods do not have to align with calendar months or weeks. A fiscal year may, for instance, be between July 1 — June 30 the following year. Hence, comparing data across the fiscal period can be challenging.
However, by building Power BI measures that compare the current and last fiscal periods, you can better analyze your data to spot trends and patterns, and to make informed business decisions.
Creating a Fiscal Calendar Table in Power BI
The first step in creating a current and last fiscal period measure in Power BI is to create a calendar table — a table that contains a row for each date in a given range. You can use this table to create relationships with other tables that contain data.
To create a calendar table in Power BI, go to the “Modeling” tab, and click on “New Table” in the ribbon. Then enter the formula. Here is a sample:
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2023, 12, 31 ) ),
"FiscalYear", YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 7, 1, 0 ),
"FiscalQuarter", CEILING ( MONTH ( [Date] ) / 3, 1 ),
"FiscalMonth", MONTH ( [Date] ),
"FiscalWeek", WEEKNUM ( [Date], 2 )
)This formula creates a calendar table called “Calendar” that spans from January 1, 2020 —December 31, 2023.
- The ADDCOLUMNS function adds four columns to the calendar tables: “FiscalYear,” “FiscalQuarter,” “FiscalMonth”, and “FiscalWeek.”
- The “FiscalYear” column calculates the fiscal year from the calendar year and month of each date, and if the month is July or later, it adds 1 to the calendar year to determine the fiscal year.
- The “FiscalQuarter” column calculates the fiscal quarter by dividing each date’s month by 3 and rounding up.
- The “FiscalMonth” column simply assigns the numeric month value to each date in the Calendar table.
- The “FiscalWeek” column calculates the fiscal week number using the WEEKNUM function. The second parameter is set to 2, specifying the week starts on Monday —typically used in fiscal calendars.
Creating a Current Fiscal Period Measure
Now that you have created a calendar table, you can now create a measure that calculates data based on the current fiscal period using DAX — a formula language in Power BI to create custom calculations.
Now to create a current fiscal period measure in Power BI, click on “New Measure” in the “Modelling” tab ribbon, and enter this formula in the formula bar:
Current_Fiscal_Period =
MAX('Calendar'[Fiscal Year]) & "-" & MAX('Calendar'[Fiscal Quarter])This formula retrieves the maximum fiscal year and a fiscal quarter from the ‘Calendar’ table and combines them to form the current fiscal period.
- The MAX function restricts the analysis to only the current fiscal year and quarter.
Define the Last Fiscal Period Measure
Now, to define the last fiscal year, you only need to subtract 1 from the current fiscal year. To create a last fiscal period measure in Power BI, click on “New Measure” in the “modelling” tab, and enter this formula:
Last_Fiscal_Year = Current_Fiscal_Year - 1- The formula subtracts 1 from the current fiscal year to calculate the last fiscal period based on the values of the current fiscal year and the current fiscal quarter.
If the current fiscal quarter is 1, it subtracts 1 from the current fiscal year to get the last fiscal year and sets the last fiscal quarter to 4. Otherwise, it subtracts 1.
Using the Measures in Power BI Visuals
Now that you have created the current and last fiscal period measures, you can then use them both in your visuals and reports.
For example, you could create a line chart that shows revenue by fiscal period. By using the current and last fiscal period measures, you can easily compare revenue across different periods and identify trends.
To use the measures in Power BI visuals, simply drag and drop them onto the visual, or the values field in the “Visualizations” pane and then customize accordingly, such as changing the chart type adding filters, etc.
Tips for Effectively Using Fiscal Period Measures
While creating fiscal period measures in Power BI can be a powerful tool for gaining insights into your business’s performance, there are some best practices to keep in mind.
Firstly, make sure that your fiscal period measures align with your organization’s financial reporting periods. For instance, if your fiscal year starts in July, make sure that your measures reflect that.
Also, you can consider using a date table instead of a calendar table. It has extra columns including the day of the week, month, and quarter, which makes it easier to create visualizations that show data by month or quarter.
Finally, be consistent in your use of fiscal period measures. Make sure to use the same measures across all your Power BI visuals and reports to avoid confusion.
Best Practices for Data Analysis in Power BI
In addition to using fiscal period measures, there are some best practices to keep in mind when analyzing data in Power BI.
To start with, make sure that your data is clean and valid. Also, do not forget to follow best visualization practices such as choosing the right chart type, keeping visuals simple and uncluttered, strategic use of colors, etc.
Finally, be aware of your audience. Ensure your visuals and reports are suited to the needs of your stakeholders. You must know the insights are they looking for, the questions they need to be answered, and so on.
Power BI Resources for Further Learning
If you’re looking to learn more about Power BI and how to maximize your data insights, there are many resources available. Here are a few to get you started:
- Microsoft Power BI Documentation: This is the official documentation for Power BI, and includes tutorials, how-to guides, and reference material.
- Power BI Community: This is a community of like minds where you can ask questions, share ideas, and learn from other Power BI users.
- Power BI YouTube Channel: This YouTube channel includes videos on a variety of Power BI topics, including tutorials, best practices, and customer stories.
Conclusion
By following these steps, you can create a measure that accurately returns the current fiscal year and the last fiscal year based on your fiscal calendar, allowing you to perform year-over-year growth calculations and other financial analyses.
Kindly remember to always update the measure as needed in case your fiscal year changes in the future.
Thanks for reading!