By Asghar Ajani
Navigating Financial Data With Ease: Creating Fiscal Year Hierarchies In Power BI
In today’s data-driven business environment, organisations rely heavily on powerful data visualisation tools to analyse and interpret information to make informed decisions. One such tool is Power BI, a versatile business analytics solution that enables users to transform raw data into meaningful insights. A common requirement for businesses, especially in the finance sector, is to analyse data based on their fiscal year rather than the calendar year.
In this post, we will explore how to configure Power BI to display data in alignment with an organisation’s financial year using the DAX language, specifically focusing on how to create a financial calendar hierarchy for effective navigation and analysis.
In this scenario, the estimated close date field was utilized in a slicer to filter opportunities that were closed within a specific timeframe. By default, Power BI drills down the date field hierarchy into years, quarters, months, and days, displayed in accordance with the calendar year. However, organizations may prefer the date slicer to display the financial year instead. To accomplish this, we can employ the DAX language to generate a new table that divides years, quarters, months, and days according to the financial year, where the first quarter commences on the 1st of July rather than the 1st of January.
To implement this functionality, we will utilize the DAX language to create a new table called “Financial Calendar.”
The DAX expression would include the column names and the data reflected in those columns.
- “Year” column to extract the calendar year from the date.
- “Month Name” column to format the date and display the month name.
- “Fiscal Month” column to determine the corresponding fiscal month based on the starting month of the fiscal year.
- “Fiscal Quarter” column to calculate the fiscal quarter by dividing the fiscal month by 3 and adding 2, with the result formatted as “QTR #” (e.g., QTR 1).
- “Fiscal Year” column to display the fiscal year in the format “FY YY/YY”, where YY represents the last two digits of the year.
- “Month” column to extract the month number from the date.
By establishing a one-to-many relationship between the new “Financial Calendar” table and the original “Est. closed date” field, the “Est. closed date” field can be configured to display the financial year in the slicer on the dashboard.
Calendar =
VAR StartYear = 2015
VAR EndYear = 2025
VAR YearStartMonth = 7 -- Define the starting month of the fiscal year
VAR QuarterMonths = {7, 10, 1, 4} -- Months indicating the start of each quarter
VAR CalendarDates = CALENDAR(DATE(StartYear, 1, 1), DATE(EndYear, 12, 31))
RETURN
ADDCOLUMNS(
CalendarDates,
"Year", YEAR([Date]),
"Month Name", FORMAT([Date],"MMMM"),
"Fiscal Month",
IF(Month([Date]) > 6, Month([Date]) - 6, Month([Date]) +6),
"Fiscal Quarter",
"QTR " & FORMAT(INT(DIVIDE(IF(Month([Date]) > 6, Month([Date]) - 6, Month([Date]) +6) + 2 , 3)),"#")
,
"Fiscal Year",
"FY " & RIGHT(IF(MONTH([Date]) >= YearStartMonth, YEAR([Date]), YEAR([Date]) - 1),2) & "/" & RIGHT( (IF(MONTH([Date]) >= YearStartMonth, YEAR([Date]), YEAR([Date]) - 1))+1,2),
"Month", MONTH([Date])
)
Power BI’s adaptability and the capabilities offered by DAX make it an invaluable resource for organisations seeking to analyse sales data within the context of their fiscal year. By creating a custom financial calendar hierarchy and establishing a relationship between the “Est. close date” field and a dedicated Fiscal Year table, organizations can gain a more accurate and comprehensive understanding of their sales performance. This approach enables businesses to align their data analysis with their specific financial cycles, leading to better-informed decisions and strategies that drive success in a competitive market.
Featured Insights
Stay Informed with ITKnocks'
Latest Technical Articles
Let's Shape the Future Together!
Ready to shape the future of your business? Connect with ITKnocks, your catalyst for innovation. Let’s collaborate and transform possibilities into reality. Contact us, and let the possibilities unfold!
