Rolling 28 Days Measure

[davehus]

Hi, I'm having an problem with a measure. I want to get a rolling 28 days downtime from a production table. There is a filter on the page which is Year Week Slicer. This defaults to 'Prior Week' on opening the report and the user can selected a week number as normal in the format 202412 as required. I need the user to see a rolling 28 day downtime in a weekly bar based on their selection, however when I try to remove the Year Week Slicer it doesn't work and just displays the previous weeks data and nothing more. My dax code is as follows: 

Rolling 4 Week Downtime =

CALCULATE (

    SUM ( 'Downtime Table'[Downtime] ),

    FILTER (

        ALL ('Downtime Table'[ProductionDate] ),

        'Downtime Table'[ProductionDate]

            >= SELECTEDVALUE ( 'Downtime Table'[ProductionDate] ) - 28

    ),REMOVEFILTERS('Date Table'[Week Slicer])

)

Any help would be appreciated.

[Anonymous]

Hi, @davehus 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[dharmendars007]

Hello @davehus , 

Please us the below modified DAX..

Rolling 4 Week Downtime =
CALCULATE (
SUM ( 'Downtime Table'[Downtime] ),
FILTER (
ALL ( 'Downtime Table'[ProductionDate] ),
'Downtime Table'[ProductionDate] >=MAX ( 'Downtime Table'[ProductionDate] ) - 28 &&
'Downtime Table'[ProductionDate] <=MAX ( 'Downtime Table'[ProductionDate] )),
REMOVEFILTERS('Date Table'[Week Slicer]))

1. MAX instead of SELECTEDVALUE: This ensures the rolling window is calculated based on the most recent production date, regardless of slicer filters.

2. Range Filter: I’ve added a <= MAX() condition to ensure that only the dates within the last 28 days are summed.

3. Filter on All Production Dates: ALL removes existing filters on the date column to correctly apply the rolling logic.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[davehus]

Hi @dharmendars007 , 

This didn't work I'm afraid. I've tried similiar dax code to the one you've supplied. It still is filtered to the week slicer when implemented.