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('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:

 

 

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.

9 Replies

  • 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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

  • 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 

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

    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.

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

     

     

  • 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?