cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
APM
Helper I
Helper I

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

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!

1 ACCEPTED SOLUTION
DataZoe
Super User II
Super User II

@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.

 

2021-02-25_CalendarSwitching.gif

 

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.

DataZoe_3-1614301065778.png

 

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.

 

FormatMonthDate.png

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

6 REPLIES 6
Icey
Community Support
Community Support

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
Super User II
Super User II

@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.

 

2021-02-25_CalendarSwitching.gif

 

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.

DataZoe_3-1614301065778.png

 

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.

 

FormatMonthDate.png

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

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. 

 

APM_0-1614347708951.png

Or do I need to do something else?

 

 

DataZoe
Super User II
Super User II

@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. 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

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

 

AB
edhans
Super User III
Super User III

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors