Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PAPutzback2
Helper II
Helper II

How do I switch out 13 fiscal calendars that won't create a dax nightmare?

I have an accounts that each use a different fiscal calendar to aggregate their data. So when I select xerox I want to use the calendar based on 7/1/2020 - 6/29/2021. If I slice to another account I might need to use the standard 1/1/2020 - 12/31/2020 calendar. 
In my account table I added a column fiscal_pattern_code which is used to filter the calendar table. All the fiscal calendars  are combined in one table tall table and have the fiscal_pattern_code and an additional attribute Calendar_type. There is also a bridge table with the complete calendar of unique dates to link to the fact table, . I also have to have the ability to override the fiscal calendar filter to use the standard 1-1 through 12-31 calendar. So I have a slicer based on the Calendar type to filter the tall fiscal table. 

Here is my calculation 

 

SUM Account Sales Orders = 
IF(SELECTEDVALUE('Dim Calendar'[Calendar Type]) = "Fiscal",
Calculate(SUM('Fact Sales Orders'[salesorders_grand_total]), 'Dim Calendar'[fiscal_pattern_code] = MAX('Dim Account'[fiscal_pattern_code])),
Calculate(SUM('Fact Sales Orders'[salesorders_grand_total]), 'Dim Calendar'[Calendar Type] = "Standard"))

 

This works, but I am hoping there might be a cleaner solution because the problem I am running into is creating all of the other time intelligence measures. Previous quarter, previous month, year over year. I keep getting blank values. So I don't know if it is my measure formulas or the way I have setup my calendar or relationships. 

PAPutzback2_0-1658845387512.png

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@PAPutzback2 , I usually use rank to create time intelligence in such case. All column of FY and STD can be one table and you use Month Rank, FY Month Rank etc for time intelligence 

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

https://amitchandak.medium.com/cheat-sheet-power-bi-4-4-4-and-4-4-5-calendar-786f76da7d92

 

example

Column

Month Rank = RANKX(all('Date'),'Date'[Month],,ASC,Dense)

 

measure
This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

View solution in original post

2 REPLIES 2
PAPutzback2
Helper II
Helper II

I see what you are doing in the video but I am wondering if the only catch is for each calculation I would have to add to more filters, one is to filter out the calendar based on the selected account. Because we might want to give the option to look at the data using the standard or the accounts fiscal calendar, I would have to use variables to catch a slicer value with two values, 'Standard' and 'Fiscal'. Then use an if statement to set another variable to grab the accounts calendar type if they select 'Fiscal'. Then add a filter to your formula - ,'Date'[Calendar Type] = varCalendarType). 
I'll experiment with that. Thanks for the quick feedback.

amitchandak
Super User
Super User

@PAPutzback2 , I usually use rank to create time intelligence in such case. All column of FY and STD can be one table and you use Month Rank, FY Month Rank etc for time intelligence 

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

https://amitchandak.medium.com/cheat-sheet-power-bi-4-4-4-and-4-4-5-calendar-786f76da7d92

 

example

Column

Month Rank = RANKX(all('Date'),'Date'[Month],,ASC,Dense)

 

measure
This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.