Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Cumulative Total DAX Measure Differences

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!



2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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!