Forum Discussion
Cumulative Sum based on multiple filter context
Hi Team ,
Need one help on one scenario in DAX.
I want to create a measure for cumulative sum which can Dynamically accept the external filter context without hardcoding in measure. Below measure is perfectly working fine but this solution is like hardcoding field names( 'Table 2'[Column1], 'Table 2'[Column2], 'Table 2'[Column3]) in allexcept is not appropriate solution.
Our requirement is to generate cumulative sum of total sales based on multiple filter context dynamically instead of hardcoding field names in all except.
measure =
Calculate(
[Total Sales],
filter(
Allexcept('Table 1',
'Table 2'[Column1],
'Table 2'[Column2],
'Table 2'[Column3]),
'Table 1'[order Date]<=MAX('Table 1'[order Date])
)
)
Please let us know if this can be done by any other approach.
Thanks
1 Reply
- amitchandakSuper User
shubhamc_002 , You should use a separate date table and try
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
or
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41
You can consider Window function
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
Power BI Webinar- How to take advantage of New Power Bi Functions Offset,Window, Index:
https://youtube.com/live/exk8F5ka1Nw