Creating a slicer to toggle between Fiscal and Calendar year and report data for respective dates

[APM]

Hello,

I have a data set with sales data from dates between 1/1/2017 through 12/31/2020.

I want to be able to report this data to different departments. Some departments use Fiscal year and some use Calendar year. 

So that I can report in the respective departments'  language, I'd like it so that I have a slicer that toggles between 'Fiscal' and 'Calendar' year and another one that has "2017", "2018", "2019", "2020" options (this latter part I already know how to do, of course).

Ultimately, what I want is that if I use the slicer and select "fiscal" and then "2018", it would pull data from April 1st, 2017 - March 31st, 2018. Likewise, if I were to select "Calendar" and "2018", it would pull data betwen January 1, 2018 -  December 31, 2018.

I'm completely lost, does anyone know how I would do this? I would use it in a lot of reports and in an executive-facing dashboard, so I'd like to be able to replicate it across reports too. 

Thanks!

[Icey]

Hi @APM ,

Does what @DataZoe  provided solve your problem?

If it does, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,

Icey

[DataZoe · Accepted solution]

@APM You can create an additional table off your date table to have both years and the ranges.  I've included a PBIX file with this set up.

The Date table I used:

Date =

ADDCOLUMNS(

CALENDAR(date(2019,1,1),date(2022,12,31)),

"Month", date(year([Date]),month([Date]),1)

)

then the additional table, Switch Calendar:

Switch Calendar =

var startmonth = 4

return

union(

ADDCOLUMNS(

SUMMARIZE('Date','Date'[Date]),

"Year", if(month([Date])>=startmonth,date(year([Date]),1,1),date(year([Date])-1,1,1)),

"Calendar", "Fiscal"

)

,

ADDCOLUMNS(

SUMMARIZE('Date','Date'[Date]),

"Year", date(year([Date]),1,1),

"Calendar", "Calendar"

))

then I set it up with a bi-directional filter on the [Date] column in each table.

Edit: I changed it to reference the Date table instead, so if you wanted to change the range, you only have to in one place!

Edit 2: Re-reading your post, your fiscal calendar starts in April, so just changed the start month to 4.

Final note, in the Date table I havethe Month as a date, which I then formated custom to be in the mmm yyyy format on the modeling tab. This also allows for you to use it as either continuous or categorical in the x-axis on charts.

[APM]

Thanks! And so, when I import my data, I add the relationship so that the data in my Sales dataset is tied to either the date column in the Date table or the date column in the Switch Calendar table, correct? Because it won't let me create a relationship with both. 

Or do I need to do something else?

[DataZoe]

@APM Great question, yes you would want to create the relationship between the Sales data and the Date table only.  The only table Switch Calendar should have a relationship with is the Date table. 

[Amardeep100115]

i did the same process and folloe the all steps but data is not getting reflacted

[edhans]

I would approach it this way:

• Create two date tables and mark them BOTH as date tables in the data model. One is calendar based, the other Fiscal. Both must follow the rules of a date table which is you must have a record for every day from start to finish - no skipped weekends or holidays. Every day is included. Whatever other columns you want is fine - month name, year, etc.
• Those two date tables are your DIM tables (dimension) tables and would be in a 1:Many relationship with your date field in your FACT table.
• You create two slicers, one with the Calendar data you slice by (month, year, week, whatever). You format these slicers to look identical.
• You then put one slicer on top of the other so they are in the exact same space.
• You then create two bookmarks. You can read more about that here, but you will have one bookmark that shows all visuals except the fiscal slicer and the Fiscal button (below), and the fiscal slicer is cleared, and that is for the Calendar people. Then the other bookmark is the opposite. It has all visuals showing except Calendar slicer and Calendar button, and Calendar is cleared (nothing selected) and that is the Fiscal people.

You then add two buttons. You would actually do the same logic - put them on top of each other. Each button calls the other bookmark via its action properties.

To the end user, this looks like a simple slicer toggle.