cancel
Showing results 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``````

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors