Subtract 2 row values based on sliced date (year month)

[Anonymous]

Hi reader!

Im trying to figure out how to subtract 2 values in a matrix/table.

In my matrix I have this setup where I compare two dates. (For privacy sakes I've decided to not show header names)

In this instance we're looking at numbers from december 2021 and march 2022. I am using a seperate date column.

Im trying to subtract the row numbers, per column.

I've tried working with MIN / MAX date  and/or LASTDATE / FIRSTDATE but somehow firstdate/Min always returns blank.

Expected outcome would be  2520 - 2519 = 1  etc.

Do you have any suggestions on how to subtract these rows? Would love to hear it!

Kind regards,

Daniël

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a measure as follow:

Measure = IF(ISINSCOPE('Table'[Date]),SUM('Table'[value]), 
CALCULATE(SUM('Table'[value]),FILTER('Table',[Date]=MIN('Table'[Date]))) -CALCULATE(SUM('Table'[value]),FILTER('Table',[Date]=MAX('Table'[Date]))))

the final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can have measure/s, that force grand total to diff, like the example below

new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
if(not(isinscope(Date[Month Year])),
calculate( sum(Table[Value]), filter('Table', 'Table'[Date] =_max)) - calculate( sum(Table[Value]), filter('Table', 'Table'[Date] =_min)),
sum(Table[Value])
)

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

[Anonymous]

Thanks for the swift reply!

Unfortunately, this only returns the default values from the matrix. So nothing changes. Anything I need to do to change this?

What might be happening is that the minx is returning blank again, as did the "firstdate" / "min" measures I tried to use before.