Forum Discussion

navafolk's avatar
navafolk
Icon for Helper IV rankHelper IV
4 years ago
Solved

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

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('Tab...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
navafolk
Icon for Helper IV rankHelper IV
4 years ago

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?