Rolling 4 Week Total

[jdwalker5]

Hello,

I'm trying to get a rolling 4 week total for each row in a summarized table.  Example below with expected result.

Week	Item	Total	Rolling 4 Week Total
8	A	5	50
7	A	10	70
6	A	15	90
5	A	20	110
4	A	25	130
3	A	30	105
2	A	35	75
1	A	40	40

The 'Rolling 4 Week Total' column should sum the 'Total' column of the last 4 weeks.  For example, for week 8 the rolling 4 week total is a sum of 5, 10, 15, 20.

Hope I'm explaining that well enough, but let me know if more info is needed.

[Anonymous]

Hi @jdwalker5 

@amitchandak  @ThxAlot Thank you very much for your prompt reply. Here please allow me to post some of my methods.

For your question, here is the method I provided:

Here's some dummy data

“Table”

Create a measure.

Rolling 4 Week Total = 
    CALCULATE(
        SUM('Table'[Total]), 
        FILTER(
            ALL('Table'), 
            'Table'[Week] > MAX('Table'[Week]) - 4 
            && 
            'Table'[Week] <= MAX('Table'[Week])
        )
    )

 Here is the result.

Regards,

Nono Chen

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

[jdwalker5]

@Anonymous ,

Thanks for the reply! I ended up bringing in a separate dataset where I used a SQL window function to get the result I needed, but this looks like it would work.  Appreciate you looking into this!

[ThxAlot · Accepted solution]

[jdwalker5]

Thanks @ThxAlot!  I didn't realize window functions were available in DAX, but that is really good to know.  I played around with it some, but couldn't quite get it to work.  That's probably on me for missing something in the syntax.  I ended up using a SQL window function and brought in a new dataset.  Thanks again for your reply!

[amitchandak]

@jdwalker5 , Prefer to have a separate table with Week on Year week depending on the data. If this year's week creates a rank column, for only week you use week in place of week rank

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

Last 4 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

[jdwalker5]

Thanks @amitchandak ,  I did not have a chance to try your solution out, but it looks like it probably would have worked.  I ended up pulling in a new dataset and used a SQL window function, but I appreciate you taking the time to respond!