sum of values for every previous week

[abogdanov]

Hello, I'm trying to put together a formula to calculate a sum of values C for every previous week. so for the week 41 it should be a sum of all C under week 40, for week 42 the sum of C of the week 41 and so on. I've looked online but haven't found anything helpful

[v-shex-msft · Accepted solution]

Hi @abogdanov,

Here is a measure expression about calculating cumulative total on the rolling weeks based on the current year and week number, you can try it if meets your requirement:

Rolling Week =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            WEEKNUM ( Table[Date], 1 ) < WEEKNUM ( currDate, 1 )
                && YEAR ( Table[Date] ) = YEAR ( currDate )
        )
    )

BTW, if you also need to split these calculations based on the current category, you can add them into that formula with values functions:

Rolling Week =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            WEEKNUM ( Table[Date], 1 ) < WEEKNUM ( currDate, 1 )
                && YEAR ( Table[Date] ) = YEAR ( currDate )
        ),
        VALUES ( Table[Category1] )
    )

Regards,
Xiaoxin Sheng

[amitchandak]

@abogdanov , if you are looking for a measure refer my blogs

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...

in case you are lookung for colum. Try like

E = sumx(filter(Table, [date] =earlier([Date]) -7),[C])