Dynamic reset running total

[Thaysenclaes]

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?

[v-zhangti · Accepted solution]

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.

[Greg_Deckler]

@Thaysenclaes This seems like a classic Cthulhu kind of problem. Cthulhu - Microsoft Power BI Community

[Thaysenclaes]

@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  

[Greg_Deckler]

@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])