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 ;

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

    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