Switch between fiscal and regular calendar via slicer

I recently answered a request for this on the Power BI Community forums and thought I would share how to implement this in more detail. This is a variant of a solution to also give the users ability to have a slicer to show the report by other pre-set date ranges (E.g. Last 7 Days, Last 30 Days, Last 6 Months, Last Year).

Here I have 2 slicers and 1 chart. The user can click on any years and then choose if they want to see it as the fiscal year or the calendar year. This fiscal year starts in April, and is customizable to other month starts. Use it here:

The slicers are the first part of the magic, which is created by a DAX table joined to the DAX date table. The second part of the magic is that the Month in the DAX date table is a date datatype that is custom formatted to show just the three digit month.

Here is my DAX Date table:

It is sparse and deliberately excluding the year. It creates the daily dates from 1/1/2017 to 12/31/2022, but also a month date that shows 1/1/2017 for each day of January 2017 and other subsequent months.

Here is the DAX table I call Switch Calendar:

The variable for startmonth is where you can specify what month your fiscal calendar starts. It grabs a copy of the Date table and then adds in the "Year" and the "Calendar" columns.

For the fiscal year it checks to see:

1. if the month of each row is less than the start month, and if so, then provides that year (also in a date datatype, so 1/1/2017, as example).

2. If the month is greater than or equal to the start date then it will simply bring in the next year value (so 4/1/2018).

This results in marking April 2017 through December 2017 and January 2018 through March 2018 as 2018 fiscal year.

The regular calendar, noted here as Calendar, simply grabs the actual year in the date datatype.

The result is a table with a fiscal row and calendar row for each date, that is, there are two rows for each individual date.

Now we have the tables set up, join the Switch Calendar to the Date table. The default relationship is 1:M from Date to Switch Calendar, but we actually need Switch Calendar to filter Date. So we will change it from single direction to bi or both directional relationship. This is not going to create problems because every value in the Date table is also in the Switch Calendar. If there were missing dates then the bi-directional filter will exclude those dates not in both tables from the report.

Now the dates in the columns should be formatted nicer, from Column Tools > Format (in the ribbon).

• The year can simply be yyyy.

• The date to your favorite date format, mine is dd-mmm-yy.

• The month to mmm.

You found the first two in that drop down in the ribbon just fine, but if you were trying to find the mmm you will notice it's not there. So now what?

If you go to the modeling tab and find your month column and click it, the properties pane will show. Down towards the bottom of the properties is another formatting section from which you can choose "Custom" and specify whatever date format you please. (Alternatively you can also use Tabular Editor).

You may be confused as to why I'm going to all this trouble to keep them as dates instead of just changing them to text with the FORMAT(...) DAX. The reason is the date datatypes will:

1. Sort properly when the calendar is toggled between Fiscal and Calendar, which is important for this solution.

2. Give you the option to use continuous or categorical in the axis on charts. Continuous tends to allow the chart to show all values without a scroll bar even when the categorical will start to force the scroll bar on you.

Now that is set up, create the chart with a measure of your choice.

1. I choose Days because I was lazy and didn't want to bring in more data.

2. Then I added Month from the Date table and Year from the Switch Calendar table.

3. I changed the x-axis to categorical, turned off concatenate labels, and turned off the title.

4. I also made the gridlines a big thicker and darker.

5. On the chart, I did the forked drill down, and if the year and month were still concatenating I re-sorted by the year and month combined from the "..." in upper right hand corner.

You can finish formatting it how you would like it to be.

And then add the Calendar and Year slicers from the Switch Calendar table.

I am using one of the Msft Skateboard Report scrims from PowerBI.Tips, as I'm into not seeing the basic format of the Power BI reports whenever possible at the moment.

What I like about this solution is it can be added to an existing report without having to change any charts or measures already created, except maybe to change out month axis where used. Hope this one is helpful to you!