Forum Discussion
Rolling 28 Days Measure
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:
3 Replies
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
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.
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
