Forum Discussion
Dynamic reset running total
Hi all,
I Power BI rookie, and need your help for a DAX problem.
The case:
If a person over a 3 week period has more than 70.5 hour combined, then a bonus is paid. The same week can not be counted twice. The starting week is defined be the user in the report.
Example,
week 1-3 = 60 hours (no bonus, so we add move the interval one week)
week 2-4 =80 hours (Bonus paid, now thes weeks can not be counted again)
week 5-7 = 71 hours (bonus paid)
We have given each week an index, and can count the running 3 week total, but I can not seem to figure out how to reset the counter, if a bonus i paid. Down below, there should be a payout in week 45, but not 46
This is my DAX until now
IndeksUge =
Var Aar = SELECTEDVALUE(DatoerBelastning[ISO Year])
Var UgeNr = SELECTEDVALUE(DatoerBelastning[ISO Weeknumber])
Var Indeks =
CALCULATE(
MIN(DatoerBelastning[Indeks])
)
Var Beregning =
CALCULATE(
SUM(BeregningKorrigeretTimer[VarighedKorrigeret]),
FILTER(
All(DatoerBelastning),
DatoerBelastning[Indeks]<=Indeks && DatoerBelastning[Indeks]>=Indeks-2
)
)
Return
Beregning
Can I accomplish what I am trying to do?
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.
4 Replies
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])
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.
