Sum everything within a date range (cumulatively)

Hi Walt1010 ,

 

To calculate the rolling sum of sickness leave days over the past two years dynamically for any given date, you need a measure that filters the sickness leave dates within a 720-day window relative to each row’s date in the table visual. Your current approach does not dynamically adjust the date range as the table progresses. The correct way to achieve this is by using a CALCULATE function that filters the sickness leave table based on a MAX date reference from a calendar table.

Here’s the correct DAX formula:

Sickness Leave 2Y Rolling Total =
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
    COUNT(sicknesstable[sicknessdate]),
    sicknesstable[sicknessdate] >= CurrentDate - 720 &&
    sicknesstable[sicknessdate] <= CurrentDate
)

This formula defines CurrentDate as the maximum date within the current row context, ensuring that for each row in the table visual, the calculation dynamically considers the past 720 days. The CALCULATE function then filters the sickness leave table to count only those entries where the sicknessdate falls within the rolling two-year window. When used in a table visual alongside Calendar[Date], this measure will update per row, maintaining the cumulative logic as the dates progress. To ensure smooth functionality, make sure you have a separate Calendar table driving the date logic in your visual. If performance becomes an issue due to a large dataset, consider pre-aggregating sickness leave counts at the monthly level before applying the rolling sum logic.

 

Best regards,