Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sboobar
Frequent Visitor

Cumulative Total Changes with Date Hierarchy Filter

I finally found how to create a cumulative total measure, but now I'm running into the problem of the cumulative total changing when I filter to see certain dates.

 

I want the cumulative total to always start from 0, or the first production date, but still be able to only view specific points in time.

For example when I view only 2023- the cumulative total starts from January 2023, not the first production date back in 2019. The Date column used below is essentially record date.

 

Right now my measure is this:

Cumulative Water =
CALCULATE(
    SUM('MRC_DailyProdDisp'[GrossWaterProd]),
    FILTER(
        ALLSELECTED('MRC_DailyProdDisp'),
        ('MRC_DailyProdDisp'[Date] <= MAX('MRC_DailyProdDisp'[Date])
        )
    ))
4 REPLIES 4
Greg_Deckler
Super User
Super User

@sboobar You can do this using Better Running Total, using CALCULATE in a single table data model is going to give you wonky results a lot of times. Better Running Total - Microsoft Power BI Community

 

You *might* be able to get there switching your ALLSELECTED to ALL


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I was able to create the cumulative (or running total) without 'calculate' but I still have the same issues when it comes to viewing/filtering to specific time periods- my measure changes its cumulative total depending on the dates I have selected. The measure I used is below:

 

Cumulative Water =
VAR _Date = MAX(MRC_DailyProdDisp[Date])
VAR _Table = FILTER(ALLSELECTED('MRC_DailyProdDisp'),[Date] <= _Date)
RETURN
SUMX(_Table,[GrossWaterProd])
 
I want the cumulative total to no matter what start from the first date (say in 2002) and even if I'm only viewing 2023 data in a table I still want the cumulative total to start from 2002. Currently when I view only 2023 data it starts the cumulative calculation in January 2023.

This worked in SQL using Spotfire, just trying to find DAX equivalent for Power BI

 

Sum([GrossWaterProd]) over (Intersect([PropNum],AllPrevious([Date])))

Example of cumulative total changing:

Location with 2023 selected:

sboobar_0-1676559899117.png

Location with 2022-2023 selected:

sboobar_1-1676559998387.png

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors