Forum Discussion

mdgene's avatar
mdgene
Helper I
4 years ago

Cumulative Total to specific date

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 screen shot of the result below.    If I use da...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

mdgene , Try like

 

Cummulative Sold max =
VAR LastSaleDate = CALCULATE(LASTDATE('Forecast Revenue'[Closed Date]),filter(allselected('Forecast Revenue'),'Forecast Revenue'[Forecast Category] = "sold"))
RETURN
CALCULATE( 'Key Measures'[Sold],
FILTER(ALLSELECTED('Date Table'),
'Date Table'[Date] <= MAX('Date Table'[Date]) && SELECTEDVALUE('Date Table'[Date]) >[LastSaleDate] ))

 

I doubt you are using month from date table in first visual

 

or

 

Cummulative Sold max =
VAR LastSaleDate = CALCULATE(LASTDATE('Forecast Revenue'[Closed Date]),filter(allselected('Forecast Revenue'),'Forecast Revenue'[Forecast Category] = "sold"))
RETURN
CALCULATE( 'Key Measures'[Sold],
FILTER(ALLSELECTED('Date Table'),
'Date Table'[Date] <= MAX('Date Table'[Date]) && SELECTEDVALUE('Date Table'[Date]) < [LastSaleDate] ))

mdgene's avatar
mdgene
Helper I
4 years ago

Hi , I tested the two query, query 1, was returning blank. while query 2 was behaving just like my query. also I am using the date column. Thank you