Partners filmexxx.link, sextotal.net, ahmedxnxx.com, https://xnxx1xvideo.com, russianxnxx

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.

CY FY GIF

 

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.

CY linked with Sales 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..

Switch between calendar and fiscal year table

  1. This table will be a disconnected table, since dates are duplicated. Each date appears twice – once for FY and for CY.
  2. Year and the Month column from this table will be used to create a pivot table.
  3. Using the Label column I will create a slicer below

Slicer

 

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])
)
  1. Using the TREATAS function I can create an artificial relationship to filter by Calendar or Fiscal Year.
  2. 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

CY FY GIF

 

    ⬇️ Pop in your Name & Email to get the file!


     

    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



    Topics that I write about...