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.

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

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))