Running total by last N weeks

[summer18]

I currently have running total formula which works but I would also need to get the running total for last 7 weeks.  Can someone please help me on this formula?

Here's my formula for Running Total which works:

Running total in Sales =

CALCULATE(SUM('Sales'[sales_amount]),

    FILTER(

        ALLSELECTED('Date'[Week]),

        ISONORAFTER('Date'[Week], MAX('Date'[Week]), DESC)

    ))

[amitchandak]

@summer18 , Try one of these two

Running total in Sales =
CALCULATE(SUM('Sales'[sales_amount]),
    FILTER(
        ALLSELECTED('Date'[Week]),
        'Date'[Week]<= MAX('Date'[Week]) &&
		 'Date'[Week]>= MAX('Date'[Week])-7
    ))
	
Running total in Sales =
CALCULATE(SUM('Sales'[sales_amount]),
    FILTER(
        ALL('Date'[Week]),
        'Date'[Week]<= MAX('Date'[Week]) &&
		'Date'[Week]<= MAX('Date'[Week]) -7
    ))

Refer to my blog on Week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

[summer18]

Hi @amitchandak ,

The running total works if it's the same month so when it's different month the running total resets.  

The expected output is the highlighted green column:

[v-juanli-msft · Accepted solution]

Hi @summer18 

Create a column

index1 = INT(([Week]-1)/7)

Create a measure

Measure =
CALCULATE (
    SUM ( 'Table 2'[sale amount] ),
    FILTER (
        ALLSELECTED ( 'Table 2' ),
        'Table 2'[Year] = MAX ( 'Table 2'[Year] )
            && 'Table 2'[Week] <= MAX ( 'Table 2'[Week] )
            && 'Table 2'[index1] = MAX ( 'Table 2'[index1] )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.