Switch Between Calendar and Fiscal Year – Power BI
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 More on Power BI / DAX 3 Ways to Find Duplicate Values in Dimension Tables Fiscal Year Date Table in Power BI Data Modeling makes DAX easy! Display Table or Matrix Until the Selected Date Display Top N items and Others in Power BI
Copy and paste this URL into your WordPress site to embed
Copy and paste this code into your site to embed