Cumulative Spend measure only works at higher granularity

[Anonymous]

I have a measure that is calculating the cumulative spend for different budget categories, and then applying it to a visual. 

CumulativeSpend = 
 VAR varDate = SELECTEDVALUE('Budget/Spend by Category'[Date])
 VAR varProduct = SELECTEDVALUE('Budget/Spend by Category'[Budget Category])
 RETURN
    CALCULATE(
        SUM('Budget/Spend by Category'[Value]),
        'Calendar'[Date] <= varDate,
        'Budget/Spend by Category'[Budget Category] = varProduct
    )

The visual

 

I have this broken out by month, quarter, and year (all coming from a date table) so you can drill up and see the cumulative total by quarter, and then by year. This works in another visual which is not using my cumulative measure, but in the cumulative view, when I try to drill up the visual just goes blank. Any ideas on how to fix this?

[ChiragGarg2512 · Accepted solution]

The measure should be created only using date table.

Preferably use one of these four methods: 

->Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

->Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

->Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

->Cumm Based on Date =

CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

For more information refer to these video:

-> Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

-> Running Total/ Cumulative: 
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

-> Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[Anonymous]

Thanks - this was very helpful.