Cumulative Total DAX Measure Differences

[Anonymous]

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!

[Anonymous]

Maybe you need to read this:

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

Best
Darek

[Anonymous]

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!