Cumulative total that resets with slicer date

[awff]

Hi there,

I have the following measure that calculates the cumulative total (running total) of opportunity values:

cumulativeOpportunitiesPipelineUSD = 
CALCULATE(
    [opportunitiesPipelineUSD], //This is just a simple calculate(sum()) measure
    FILTER(
        ALLSELECTED('0 dim_Dates'[Date]), 
        '0 dim_Dates'[Date] <= MAX('0 dim_Dates'[Date])
    )
)

I also have a date slicer that has three levels - year, quarter and month.

I essentially need the cumulative totals to reset for the period selected on the slicer. Currently, it continues off from the pervious periods as you can see below.

I really only need it to reset every period selected. So when I select 2023 Q3 then it will start from zero to the end of Q3, and if I select Q4 it will only show me the cumulative total starting from October to December. Same with if I select 2023 or 2024. I hope this makes sense...

[danextian · Accepted solution]

Hi @awff ,

In the sample formula below, the cumulative resets only if a period is selected and continues to accumulate from the earliest ALLSELECTED date if not period is selected.

Reset = 
VAR __MIN =
    CALCULATE ( MIN ( Dates[Date] ), ALLSELECTED ( Dates ) )
VAR __MAX =
    MAX ( Dates[Date] )
RETURN
    CALCULATE (
        [Sum of Values],
        FILTER ( ALLSELECTED ( Dates ), Dates[Date] >= __MIN && Dates[Date] <= __MAX )
    )

You can add a condition to it so it shows a different value if no period is selected

Reset =
VAR __MIN =
    CALCULATE ( MIN ( Dates[Date] ), ALLSELECTED ( Dates ) )
VAR __MAX =
    MAX ( Dates[Date] )
RETURN
    IF (
        NOT ( HASONEVALUE ( Dates[Period] ) ),
        [other value],
        CALCULATE (
            [Sum of Values],
            FILTER ( ALLSELECTED ( Dates ), Dates[Date] >= __MIN && Dates[Date] <= __MAX )
        )
    )

[voodoo23]

Hello, i have same problem with cumulative sum, I have slicers and i need show the cumulative sum consider two relationships (Plan and fact). Can you Expain me what mistake i do?

[awff]

Works perfectly! Thank you so much!