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.
Hi All,
I have a measure (Measure 1) that shows some sales by week in matrix table - always last 10 weeks.
Then I need a create a second measure which will show some calculation besed on the previous measure, so sum of previous weeks.
So I need something like that:
If Measure1 - sum of previous weeks < 0, 0, Measure1 - sum of previous weeks
First week from the list will not subtract anything.
Week 2 from the list would show: Measure 1 for week 2 minus value of Measure 1 in week 1 from the list.
...
Week 8 would show: Measure 1 for week 8 minus a sum of measures from week 1 to 7 etc.
Example from excel:
I have no idea how to do that correctly in power bi.
Please support.
Thank you!
You'd want to start with a cumulative total, there's a lot of solutions on those but here's one for your case:
Cumulative Total =
VAR _week = SELECTEDVALUE('YourTable'[Week])
RETURN
CALCULATE([Measure 1], FILTER(ALL('YourDataTable'), [Week] < _week))
This should give you the cumulative sum of all the weeks previous to the current one in the matrix (which is why there is a < as opposed to a <= like for normal cumulative totals). I notice you're only wanting the most recent 10 weeks so that should look like this:
Cumulative Total =
VAR _week = SELECTEDVALUE('YourTable'[Week])
VAR _max_week_TY = CALCULATE(MAX('YourTable'[Week]), FILTER(ALL('YourDataTable'), Year = YEAR(SELECTEDVALUE('YourDateTable'[Date])))
VAR _10_weeks_ago = IF(_max_week_TY - 10 < 1, 1, _max_week_TY - 10)
RETURN
CALCULATE([Measure 1], FILTER(ALL('YourDataTable'), [Week] < _week)
&& [Week] >= _10_weeks_ago
&& YEAR('YourDateTable'[Date]) = YEAR(SELECTEDVALUE('YourDateTable'[Date])))
The above is also considering the year context (would be easier if you had a column for year but I built that assuming that you don't). Then you'd just need to do:
Answer = [Measure 1] - [Cumulative Total]
The only thing this is really missing is the ability to include weeks from the end of the previous year if we are early into the current year and there haven't been 10 weeks yet, you could achieve that with some extra logic though, let me know if that works.
Thank you @TobyNye
It is not working for me. It is complicated because I need to calculate cumulative and rolling totals with some conditions.
Not sure if it is better to create a table based on what I need and do calculations in calculated columns...
Thank you!
@A_a_a , How is you data actually present in Power BI. Do you have Date or Year Week or only week?
In most the cases, we have a separate date/week table join back with the table
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Thank you @amitchandak
It is not working here because I need to calculate cumulative and rolling totals with some conditions, however it was very helpful for my other issues!
Thank you!
A_a_a
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
91 | |
89 | |
79 | |
69 | |
68 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |