Working with multiple fiscal calendars

[nrosewarne]

Hello,

I am working with 3 seperate calendars; 

Standard EOYMonth = 12

Fiscal 1 EOYMonth = 3

Fiscal 2 EOYMonth =9

What I hope to be able to do is use one slicer at the top of a report page to select (single select only) the calendar that the user wants to view. Then from that selection limit which Date Hierachy will be used in a subsequent slicer. I hope that I will be able to use the "Hierarchy Slicer" (https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380820?tab=Overview) as the second slicer. 

I have tried using multiple calendar tables, one table with all calendars and have not had any success thus far. Any suggestions from the community are greatly appreciated, or any thoughts I may have overlooked

Thanks,

Nick

[Icey · Accepted solution]

Hi @nrosewarne ,

I find something interesting. Can this help you?

Create Dynamic Periods for Fiscal or Calendar Dates in Power BI

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nrosewarne]

@Icey  Thank you for sharing this, at a glance it does seem like this will put me on the right track for solving the users needs. Unfortunately the task I was working on at the time was a non-critical convenience request, and as I have just started my 4 week sprint I won't have time to look at that work item again for about a month. I look forward to being able to add this functionality to many of my reports in the future.

Thanks again for your help!

Regards,

Nick

[Icey]

Hi @nrosewarne ,

One workaround which may meet your requirements:

1. Create a slicer table which is independent of other tables.

2. Create 3 measures. Refer to this blog: Show or Hide a Power BI Visual Based on Selection.

Make Transparent 1 = IF ( SELECTEDVALUE ( Slicer[Calendar] ) = "Calendar 1", "#FFFFFF00", "White" )

Make Transparent 2 = IF ( SELECTEDVALUE ( Slicer[Calendar] ) = "Calendar 2", "#FFFFFF00", "White" )

Make Transparent 3 = IF ( SELECTEDVALUE ( Slicer[Calendar] ) = "Calendar 3", "#FFFFFF00", "White" )

3. Create a blank measure and create a card using it.

Measure = BLANK()

4. Set conditional background of the card.

5. Result.

PBIX file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.