Changing Subtotal Aggregations for Measures in a Matrix Visual

[jaartz]

Hi everyone,

I have a dataset that looks like this:

This is currently represented in a matrix visual. The subtotal of the first row returns the first value in the row and the subtotal of the last row returns the sum. I'm attempting to return the last value in the last row as the subtotal for that aggregated period. The DAX measure for the first row is CALCULATE(SUM(Fact_Table[Amount]), PREVIOUSMONTH(Dim_Date[Date])) and, I guess because it uses PREVIOUSMONTH, DAX evaluates that to January as the aggregation, which is my desired result. The DAX measure for the last row is simply SUM(Fact_Table[Amount]). I understand that this defaults the aggregation of the subtotal to a sum, but I'm a bit stumped about how to change that aggregation for the subtotal shown to what I'd really like it to be: the last value available in that subtotal period (i.e. $645 should be the subtotal for the last row, not the sum of all values). I've reviewed this post, but that, as expected, returns the max of the month/year in the date table as a whole and the corresponding value. Really all I'm attempting to do is use the last available month in the last column as the aggregation for that subtotal. Any help with this would be greatly appreciated. Thank you!

[jaartz]

Thank you!

[amitchandak · Accepted solution]

@jaartz , One option is to use

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Why previousmonth does not give result when datesmtd is giving it: https://youtu.be/1KkoJehRVeg

Another is swich using isinscope

if(isinscope (Date[Month Year]) , CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) , CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month)) )

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/