topic Re: Cumulative Total DAX Measure Differences in DAX Commands and Tips

Cumulative Total DAX Measure Differences

Anonymous — Thu, 15 Aug 2019 19:09:39 GMT

Hello,

I've created two different DAX Measures as follows:

Cumulative Total := CALCULATE(

sum('DataTable'[Amount]),

FILTER(ALLSELECTED('DataTable'),

'DataTable'[Invoice Date]<=MAX('DataTable'[Invoice Date])

)

and

Cumulative Total Test := CALCULATE(

sum('DataTable'[Amount]),

FILTER(ALLSELECTED('Calendar'),

'Calendar'[Date]<=MAX('Calendar'[Date])

))

The Cumulative Total Test measure allows me to breakdown data by Product/Class; and when I filter by a specific year, it provides the cumulative total over the year.

The Cumulative Total measure does not allow me to breakdown data by Product/Class and gives me the same value for most rows, (I have a weird product row that isn't calculating correctly) but it gives me the correct cumulative total for the year including YTD if I filter the year.

Can someone please help explain the differences between these two measures? Seems weird to me that they wouldn't give the same response as the Calendar's Date & DataTable's Invoice Date are related.

Thanks in advance!

Re: Cumulative Total DAX Measure Differences

Anonymous — Fri, 16 Aug 2019 09:12:11 GMT

Maybe you need to read this:

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

Best
Darek

Re: Cumulative Total DAX Measure Differences

Anonymous — Mon, 19 Aug 2019 13:09:09 GMT

Thanks for the article Anonymous! Pretty new to Power BI so most of the article was lost on me.

My guess is that since Class/Product are on the Data Table, when I use ALLSELECTED it ignores the filter context set by my slicer for Class/Product which is why I cannot calculate the total amount of a specific class. When I use the Calendar table, it allows me to use the slicer relationship to provide filter context. Does that seem right?

Thanks for your help!