topic Rolling Sum for a subset of data in DAX Commands and Tips

Rolling Sum for a subset of data

MXSven — Mon, 19 Feb 2024 07:47:48 GMT

Hi Everybody,

I am struggling with a DAX Formula:

Calculation works for the overall time range, as you can see in the table,
but it doesn't work for specified time range (15th to 23rd ).

The rolling sum for this time range should start with 0 instead of 1

This is my DAX Formula:

Cumulative Rolling Sum Value_B =
CALCULATE(
SUM('Sheet1'[Value_B]),
FILTER(
ALL('Sheet1'),
'Sheet1'[Date] <= MAX('Sheet1'[Date])

)
)

Any ideas what I am doing wrong ?

Re: Rolling Sum for a subset of data

123abc — Mon, 19 Feb 2024 13:30:30 GMT

It seems like you're trying to calculate a cumulative rolling sum for a specific time range in your data using DAX. The issue you're encountering is that your current formula calculates the rolling sum starting from the beginning of the dataset, whereas you want it to start from 0 for a specified time range.

To achieve the desired result, you need to modify your DAX formula to conditionally start the rolling sum from 0 for the specified time range. You can achieve this by adding an additional condition to your FILTER function.

Here's how you can modify your DAX formula:

Cumulative Rolling Sum Value_B =
VAR StartDate = DATE(2024, 1, 15)
VAR EndDate = DATE(2024, 1, 23)
RETURN
CALCULATE(
IF(
MIN('Sheet1'[Date]) > StartDate,
0,
SUM('Sheet1'[Value_B])
),
FILTER(
ALL('Sheet1'),
'Sheet1'[Date] <= MAX('Sheet1'[Date]) &&
'Sheet1'[Date] >= StartDate &&
'Sheet1'[Date] <= EndDate
)
)

In this modified formula:

I used two variables, StartDate and EndDate, to define your specified time range.
I used the IF function to check if the minimum date in your data is greater than the start date of the specified time range. If it is, the rolling sum starts from 0; otherwise, it calculates the sum normally.
I adjusted the FILTER function to include the conditions for the specified time range (between 15th and 23rd January 2024).

Please replace the StartDate and EndDate with your actual start and end dates. This formula should now calculate the cumulative rolling sum starting from 0 for the specified time range.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Rolling Sum for a subset of data

MXSven — Tue, 20 Feb 2024 07:33:01 GMT

Thanks, 123abc.
I tried your proposed solution, but it is not calculating as I expected

Cumulative Rolling Sum Value_B =
VAR StartDate = MIN('Sheet1'[Date])
VAR EndDate = MAX('Sheet1'[Date])
RETURN
CALCULATE(
IF(
MIN('Sheet1'[Date]) > StartDate,
0,
SUM('Sheet1'[Value_B])
),
FILTER(
ALL('Sheet1'),
'Sheet1'[Date] <= MAX('Sheet1'[Date]) &&
'Sheet1'[Date] >= StartDate &&
'Sheet1'[Date] <= EndDate
)
)

The only thing I did differently was to retrieve the start date and end date from the sliders, and I checked the values.