At times you may have to toggle between presenting your data by fiscal year or calendar year. And let me tell you that this isn’t natively possible in Power BI unless you read this post 😉
In this post, I’ll share an interesting technique to switch between calendar year and the fiscal year (for any visual).
Switch Between Calendar and Fiscal Year in Power BI – Video
This trick is ideal for a few selected calculations that need to toggle between CY and FY and NOT for the entire Data Model.
Before we begin here is how the end should look like. Using a slicer I can switch the Sales measure between calendar and fiscal year.
Now consider this Data Model
Sales linked with a regular Calendar Table. Although I have a FY table too, but I haven't linked it to any other table.
Creating a Slicer to select Financial Year
To be able to create a slicer we need to first create a table that contains relevant columns. Consider this DAX for creating a table.
FY & CalTable = UNION( SELECTCOLUMNS( 'Calendar', 'Date', 'Calendar'[Date], 'Year', 'Calendar'[Year], 'Month', 'CY ' & 'Calendar'[Month], 'Label', 'By CalYear', 'Sort', 'Calendar'[Index] * 1 ), SELECTCOLUMNS( 'F Calendar', 'Date', 'F Calendar'[Date], 'Year', 'F Calendar'[FY], 'Month', 'FY ' & 'F Calendar'[Month], 'Label', 'By FisYear', 'Sort', 'F Calendar'[Index] * 13 ) )
here is how the table looks like..
- This table will be a disconnected table, since dates are duplicated. Each date appears twice – once for FY and for CY.
- Year and the Month column from this table will be used to create a pivot table.
- Using the Label column I will create a slicer below
The Calculation
Once the slicer is created, any calculation that goes in the pivot table should get sliced by the calendar or financial year.
Sales =
CALCULATE(
[Sales Base Measure],
TREATAS(VALUES('FY & CalTable'[Date]), Sales[Date])
)
- Using the TREATAS function I can create an artificial relationship to filter by Calendar or Fiscal Year.
- This technique would work well to support a handful of calculations for the primary reason that TREATAS function is less optimal as compared to the regular one-to-many relationships.
The Final Output
Once everything is set up, the result seems like this
