Forum Discussion

Frequent Visitor

3 years ago

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

Icon for Community Champion rank

Community Champion

3 years ago

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

sboobar
Frequent Visitor
3 years ago
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.
- sboobar
  Frequent Visitor
  3 years ago
  Example of cumulative total changing:
  Location with 2023 selected:
  Location with 2022-2023 selected:
- sboobar
  Frequent Visitor
  3 years ago
  This worked in SQL using Spotfire, just trying to find DAX equivalent for Power BI
  
  Sum([GrossWaterProd]) over (Intersect([PropNum],AllPrevious([Date])))