Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

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

Add additional filter and try

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.

## Helpful resources

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors