Rolling by Week

[Henrik_99]

Hi

I am trying to create a rolling sum between Monday and Sunday as shown below in column "Rolling earned". How do you calculated this value. 

Earned week is calculated as following:

Rolling Earned Week = CALCULATE([Earned2],    FILTER (

        ALL ( 'DateTable'[EoWDate] ),

        YEAR ( 'DateTable'[EoWDate]) = YEAR ( MAX ('DateTable'[EoWDate] ) )

            && 'DateTable'[EoWDate] <= MAX ( 'DateTable'[EoWDate] )

    )

)

Henrik

[Henrik_99 · Accepted solution]

Excellent, working fine.

Thank you, Mikelytics...;)

[mangaus1111]

Hi @Henrik_99 ,

maybe this works for you

Measure = 
VAR MinDate = MIN('Facts8'[Date])
VAR MaxWeek = MAX('Facts8'[Week])
RETURN
SUMX(
     FILTER(
          ALL('Facts8'),
           MaxWeek = 'Facts8'[Week] && MinDate >='Facts8'[Date]
           ),
    'Facts8'[Earned]
)

this is the table that I have used

and this is the result

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mikelytics]

Hi Henrik,

I have another approach which looks a little bit more complex in the first place but it also covers the correct calculation when the year changes if this is a requirement in your case:

Rolling weeks incl change of year = 
var var_EndDate = MAX('Dim Date'[Date])
var var_WeekDayNumber = CALCULATE(SELECTEDVALUE('Dim Date'[WeekDayNum]),'Dim Date'[Date] = var_EndDate)
var var_StartDate = var_EndDate - var_WeekDayNumber + 1

var Result =
CALCULATE(
    SUM(FactOnlineSales[SalesAmount]),
    ALL('Dim Date'),
    DATESBETWEEN('Dim Date'[Date],var_StartDate,var_EndDate)
)

RETURN
Result

Please find below the result.

Best regards

Michael

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.