Need help using a slicer/filter on a calculated rolling sum column

[ddetzler]

I have been stumped for days on this one. Please help. Below is an example of the table I am working from.

I am needing to create a rolling 7 day average of sales, and plot that on a graph. <-- This part I have been able to do with the below Columns:

Sales Rolling 7 Day =
CALCULATE (
SUM (Daily[Sales (Daily)]),
DATESINPERIOD ( 'Daily'[Date], LASTDATE ( 'Daily'[Date] ), -7, DAY ),
ALLEXCEPT ( Daily, Daily[Sales (Daily)]) )
)

THEN:

Avg. Sales Rolling 7 Day = [Sales Rolling 7 Day]/7

This part works, and I am able to look at total sales at a rolling 7 day avg. view. The problem occurs when I try to add a slicer to the page (Product_ID). When I try to filter the graph by product_ID, it does not show rolling 7 avg. for just that Product_ID, it continues to show the total. Any help would be appreciated. Thanks!

[v-piga-msft · Accepted solution]

Hi @ddetzler,

Please modify your formula like below.

Sales Rolling 7 Day =
CALCULATE (
    SUM ( 'Daily'[Sales] ),
    DATESINPERIOD ( 'Daily'[Date], LASTDATE ( 'Daily'[Date] ), -7, DAY ),
    VALUES ( Daily[Product_ID] )
)

Then you could get your desired output.

Hope this can help you!

Best  Regards,

Cherry

[ddetzler]

That did it! Thank you @v-piga-msft!