Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 :
Without the Cumulative Sum the normal measure returns data for the available dates :
Solved! Go to Solution.
@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
@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
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
@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] )) )
Thanks for your support. For my data set the other solution with SUMX was able to get the desired result.
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |