cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Cumulative total that resets with slicer date

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...

1 ACCEPTED SOLUTION
Super User

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 )
)
)
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
3 REPLIES 3
Super User

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 )
)
)
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Frequent Visitor

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?

Helper III

Works perfectly! Thank you so much!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors