Cummulative Total Up to specific date

Data Visualizations
1

Please I have a measure that shows the cumulative measure upto specific date. But My measure still show values past the date expected. I have added a a pbix file for this. I followed the enterprise dna video, but I could not get the measure to work

This is my dax measure

test.pbix (94.4 KB)
Cummulative Sold max =
VAR LastSaleDate = CALCULATE(LASTDATE(‘Forecast Revenue’[Closed Date]),‘Forecast Revenue’[Forecast Category] = “sold”)
RETURN
IF(SELECTEDVALUE(‘Date Table’[Date]) >[LastSaleDate] ,
BLANK(),
CALCULATE( ‘Key Measures’[Sold],
FILTER(ALLSELECTED(‘Date Table’),
‘Date Table’[Date] <= MAX(‘Date Table’[Date]) ) ))

Please what am I missing please. Thank you

2

Hi @akeko

Welcome to the forum !

It is possible to add a “make blank”-condition to the Cumulative Sales-measure, when there is no sales, see below:

Cumulative2 Sales = 
var CumSales = CALCULATE( SUM(Sales[Sales]),
                  FILTER(ALLSELECTED('Date Table'[Date]),
                   'Date Table'[Date]  <= MAX('Date Table'[Date])))
return 
if(isblank(sum(Sales[Sales])), BLANK(), CumSales)

I hope this answers your question,
kind regards, Jan van der Wind

3

Thanks for the solution