Sum of measures (values) for previous weeks

[A_a_a]

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!

[TobyNye]

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.

[A_a_a]

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!

[amitchandak]

@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

[A_a_a]

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