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

Anonymous
Not applicable

## Need help with Cumulative Total at the Date Grain where date is missing

Hi All,

I am trying to add a cumulative total measure for my corresponding Sum Measure.

While it is working in standard situation where months/dates are involved its not working in the date grain of my dataset.

For some reason it is introducing Blanks and i need to eliminate them to show the cumulative sum.

Unlike the traditional datasets, this data is not available for all dates but need help in addressing the problem.

Measure I Used :

Cumulative Total = CALCULATE([xxMeasureXX],
FILTER(ALLSELECTED(DIM_Date[Date]),DIM_Date[Date]<=MAX(DIM_Date[Date])))

Without the Cumulative Sum the normal measure returns data for the available dates :

1 ACCEPTED SOLUTION
Super User

@Anonymous Try something like this:

``````Better RT =
VAR __Date = MAX('DIM_Date'[Date])
VAR __Table = FILTER(ALL('Table'), [Date] <= __Date)
VAR __Result = SUMX( __Table, [Column])
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

@Anonymous Try something like this:

``````Better RT =
VAR __Date = MAX('DIM_Date'[Date])
VAR __Table = FILTER(ALL('Table'), [Date] <= __Date)
VAR __Result = SUMX( __Table, [Column])
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

For my issue, the soluion worked with ALLSELECTED rather than ALL

``````Better RT =
VAR __Date = MAX('DIM_Date'[Date])
VAR __Table = FILTER(ALLSELECTED('Table'), [Date] <= __Date)
VAR __Result = SUMX( __Table, [Column])
RETURN
__Result``````
Super User

@Anonymous , Because cumulative will give data for the dates not having any data

Cumulative Total = CALCULATE([xxMeasureXX],
FILTER(ALLSELECTED(DIM_Date[Date]),DIM_Date[Date]<=MAX(DIM_Date[Date])) , not(isblank( Table[Date] )) )

Anonymous
Not applicable

Thanks for your support. For my data set the other solution with SUMX was able to get the desired result.