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 ;

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.

Matex's avatar
Matex
Frequent Visitor
1 year ago

Works perfectly. Thank you