Forum Discussion
Dynamic reset running total
Hi, Thaysenclaes
I simulated your data to make an example, I hope it will help you. Regarding your cumulative summation function can remain unchanged, you can use the IF function to determine that the last 2 weeks above 70.5 are not part of the reward range.
Measure = VAR Week1 = SUMX ( TOPN ( 3, SUMMARIZE ( FILTER ( ALL ( 'Table' ), [Week]<=MAX('Table'[Week] )),'Table'[Week], "Sum", SUM ( 'Table'[Hours] ) ), [Week], DESC ), [Sum] ) VAR Week2 = SUMX ( TOPN ( 3, SUMMARIZE ( FILTER ( ALL ( 'Table' ), [Week]<=MAX('Table'[Week] )-1),'Table'[Week], "Sum", SUM ( 'Table'[Hours] ) ), [Week], DESC ), [Sum] ) VAR Week3 = SUMX ( TOPN ( 3, SUMMARIZE ( FILTER ( ALL ( 'Table' ), [Week]<=MAX('Table'[Week] )-2),'Table'[Week], "Sum", SUM ( 'Table'[Hours] ) ), [Week], DESC ), [Sum] ) RETURN IF(Week1>70.5&&Week2>70.5&&Week3>70.5,0,IF(Week1>70.5&&Week2>70.5,0,IF(Week1>70.5,1,0)))Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thaysenclaes This seems like a classic Cthulhu kind of problem. Cthulhu - Microsoft Power BI Community
Greg_Deckler Thank you for the reply. I am trying to adapt the method from the link, but am somewhat struggling.
Exampel:
Week 1 + 2 + 3 sums to above the threshold for bonus
Likewise for week 2 + 3 + 4.
So max index would be for week 4. But I need to reset after week 3. The next sum should be for 4+5+6. Then 6+5+7, then 7+8+9 until the threshold is reached again.
What calculate expression can execute that query? Right now the method is predicated on skips in the index
Thaysenclaes On second thought, try adding a columns like this or convert these to measures.:
Column = VAR __Min = MIN('Table'[ISO Weeknumber]) RETURN TRUNC(DIVIDE([ISO Weeknumber] - __Min, 3)) Column1 = VAR __Current = [Column] VAR __Week = [ISO Weeknumber] RETURN SUMX(FILTER('Table',[Column] = __Current && [ISO Weeknumber] <= __Week),[Hours])
