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

[Anonymous]

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 :

[Greg_Deckler · Accepted 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]

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

[amitchandak]

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

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