Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hey all - kind of loaded question here. It's been a while since I've had to use any of this here.
So I have a dataset fact table with a date column (1 day per row), and another column that shows how many calls we got on that day. This is the end result I want to come to:
Here is what my data model looks like:
Within the date table, I have created a calculated column which would show when the week ended (which is what I want it aggregated by) - here is the DAX I used to create this WeekEnd Date column for the grouping:
= dimCalendar[Date]+7-1*WEEKDAY(dimCalendar[Date],1)
This DAX gives me the calculated column I can use for the pivot/visual as it groups by Week (whenever that week ends.
My main issue now is that I want to show last 8 weeks. I created a measure to use to SUM up everything and filter it for the last 56 days (8 weeks) using this DAX:
=CALCULATE(SUM([Calls Offered]),
FILTER(
ALL('SL Data Sheet sht'[Date]),
[Date] > TODAY() - 56))
When I put everything into a PIVOT, this is what it looks like:
The highlighted number is incorrect because it's not including anything outside of that 56 day range. As days go on, it won't include the beggining of the week on 6/18 because its outside of 56 days.
From what I understand, the only correct solution from here is using the RANKX on the WeekEND Date so I know which are my last 8 periods. Then I can filter on those last 8 WeekEND date periods, instead of having to filter for last 56 days.
A long ramble here, but if anyone could help, I would really appreciate it.
Solved! Go to Solution.
Try this solution.
1. Create calculated column in date table:
Relative Week =
VAR vToday = TODAY()
VAR vWeekEndDate =
[Date] - WEEKDAY ( [Date], 1 ) + 7
VAR vWeekEndDateToday =
vToday - WEEKDAY ( [Date], 1 ) + 7
VAR vResult =
CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER )
RETURN
vResult
2. Create measure:
Rolling 8 Weeks =
CALCULATE (
SUM ( FactTable[Amount] ),
DimDate[Relative Week] >= -8,
DimDate[Relative Week] < 0
)
Proud to be a Super User!
Try this solution.
1. Create calculated column in date table:
Relative Week =
VAR vToday = TODAY()
VAR vWeekEndDate =
[Date] - WEEKDAY ( [Date], 1 ) + 7
VAR vWeekEndDateToday =
vToday - WEEKDAY ( [Date], 1 ) + 7
VAR vResult =
CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER )
RETURN
vResult
2. Create measure:
Rolling 8 Weeks =
CALCULATE (
SUM ( FactTable[Amount] ),
DimDate[Relative Week] >= -8,
DimDate[Relative Week] < 0
)
Proud to be a Super User!
I'm trying to compare the 8 weeks that preview those to see progression or regression of sales, is there a way I can create a column of comparison?
Is this a duplicate post?
Proud to be a Super User!
This is excellent - thank you so much for this. How do you begin to think through the logic? I struggle so much with this (framing the solution in my head)
Glad to hear that works. The concept of Relative columns in DimDate is the basis of the logic. Once the Relative columns are created in DimDate, measures can be easily created. A robust date table is essential to any data model.
Proud to be a Super User!
Do you know if convert and integer is able to be used in Power Pivot? It's not working in Power Pivot for whatever reason.
Not sure about Power Pivot. You can try removing CONVERT and INTEGER, or use ROUND.
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |