Measure difference of two last rows in matrix visual (reference to another measure(s))

[navafolk]

Hi pros,

My data looks like:

I am preparing visual matrix as below:

in which MTD in value is a measure: 

MTD = totalmtd(sum('Table'[Amount]),'Table'[Date],filter('Table','Table'[Date]>eomonth('Table'[source name.date],-2)))

This visual is filtered by a slicer of [source name.date].

Please help me to add a last measure row of difference between two last rows above it (2 last rows may change by slicer context, 2 last rows here are source name.date 14 Mar 2022 and 07 Mar 2022); and can the difference measure refer to MTD measure please. Super thanks.

My expected would be:

[v-yalanwu-msft · Accepted solution]

Hi, @navafolk ;

Try it.

MTD diff = 
var _max=CALCULATE(MAX('Table'[source name.date]),ALL('Table'))
var _min=CALCULATE(MAX('Table'[source name.date]),FILTER(ALL('Table'),[source name.date]<_max))
return 
IF(MAX([Date])>EOMONTH(MAX('Table'[source name.date]),-2),
   IF(ISINSCOPE('Table'[source name.date]),
     SUM([Amount]),
     CALCULATE(SUM([Amount]),FILTER('Table',[source name.date]=_max))-CALCULATE(SUM([Amount]),FILTER('Table',[source name.date]=_min))))

The final output is shown below:

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.

[v-yalanwu-msft]

Hi, @navafolk ;

I'm sorry that I don't quite understand what you mean. Maybe you can give an example to illustrate it more perfectly.

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.

[navafolk]

@v-yalanwu-msft ,

I will use your .pbix for example. For every [amount] calculation, it will only calculate month of [date] not older than previous month of [source name.date]. I.e.

- row '28 February, 2022' will only show calculation from 2022 January, not show  2021 November

- row '7 March, 2022' will only show calculation from 2022 February, not show 2022 Jannuary

Here is my measure to calculate it (it works):

totalmtd(sum('Table'[Amount]),'Table'[Date],filter('Table','Table'[Date]>eomonth('Table'[source name.date],-2)))

and it also works with your calculation, if I replace your SUM([Amount]) by it.

MTD diff = 
var _max=CALCULATE(MAX('Table'[source name.date]),ALL('Table'))
var _min=CALCULATE(MAX('Table'[source name.date]),FILTER(ALL('Table'),[source name.date]<_max))
return 
IF(ISINSCOPE('Table'[source name.date]),
   totalmtd(sum('Table'[Amount]),'Table'[Date],filter('Table','Table'[Date]>eomonth('Table'[source name.date],-2))),
   CALCULATE(totalmtd(sum('Table'[Amount]),'Table'[Date],filter('Table','Table'[Date]>eomonth('Table'[source name.date],-2))),FILTER('Table',[source name.date]=_max))-CALCULATE(totalmtd(sum('Table'[Amount]),'Table'[Date],filter('Table','Table'[Date]>eomonth('Table'[source name.date],-2))),FILTER('Table',[source name.date]=_min)))

but totalmtd(...) is repeated many times. Is there any way we can avoid this repeat by set totalmtd as var and use it in other calculation?

[v-yalanwu-msft]

Hi, @navafolk ;

You could try it.

MTD diff = 
var _max=CALCULATE(MAX('Table'[source name.date]),ALL('Table'))
var _min=CALCULATE(MAX('Table'[source name.date]),FILTER(ALL('Table'),[source name.date]<_max))
return 
IF(ISINSCOPE('Table'[source name.date]),
   SUM([Amount]),
   CALCULATE(SUM([Amount]),FILTER('Table',[source name.date]=_max))-CALCULATE(SUM([Amount]),FILTER('Table',[source name.date]=_min)))

The final output is shown below:

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.

[navafolk]

Thank you @v-yalanwu-msft,

It is super. We are very close to it.

The only last thing that I have to keep MTD measure is because: the measure only takes 'Table'[Date] before 1 month since 'Table'[source name.date].

It means that the data of 'Table'[Date] earlier more than 1 month since 'Table'[source name.date] is not appropriate.

Is there any way that can put this measure to your MTD diff measure would be perfect @v-yalanwu-msft 

[navafolk]

Hi pros,

May I try ideal of:

1. Set measure var of max and nearest max [souce name.date] from slicer:

var max_sourcedate=maxx(allselected('Table'),'Table'[source name.date])
var nearestmax_sourcedate=calculate(maxx(allselected('Table'),filter('Table','Table'[source name.date]<max_sourcedate)))

 2. Then return calculation MTD and MTD diff based on above var of max and nearest max [souce name.date]:

MTD = totalmtd(sum('Table'[Amount]),'Table'[Date],filter('Table','Table'[Date]>eomonth('Table'[source name.date],-2)))

MTD diff = calculate([MTD],filter('Table','Table'[source name.date]=max_sourcedate))-calculate([MTD],FILTER('Table','Table'[source name.date]=nearestmax_sourcedate))

[navafolk]

Thank you @amitchandak,

In my case, I cannot create another date table. That's why I have to find way of PBI measure.

Is there another way around would be great. Thanks again.

[amitchandak]

@navafolk , date should be joined with a date table

then you can have measures like

MTD = CALCULATE(SUM(Table[Value),DATESMTD('Date'[Date]))
last MTD = CALCULATE(Sum(Table[Value]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

diff= [MTD] -[Last MTD]

final measure = if(isinscope (Table[Source.name.date]), [MTD], [Diff])

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

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA