Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
@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))
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.
@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))
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
104 | |
87 | |
35 | |
35 |
User | Count |
---|---|
152 | |
98 | |
81 | |
61 | |
55 |