Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculate MTD according to slicer date

Hello, 

 

I have the following measure:

 

[Measure 100] - [Value in GBP on last day of Month (Last Month)]
 
where Measure 100 = 
CALCULATE([Total EOD Balance in GBP], FILTER(ALL('V3 Measure for Changes'),'V3 Measure for Changes'[rank]=max('V3 Measure for Changes'[rank])))
 
Currently, when I select a date on the filter (the date on which is linked from the Data table) it calculates the difference between the latest value and the value at the end of the previous month. It is always fixed as it works with a rank table. This means if I choose a day other than the last date the Measure doesn't work. 
 
I would like, to, however, to create a calculation that can be used with a date slicer so that no matter what date is chosen it will calculate:
 
= [Value at that date] - [Value at the end of the previous month]
 
Thank you. 
 
Soo
Need Help

1 Reply

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Anonymous ;

    I create a simple test, and if we want to calculate the end of the previous month value. we could.

    Measure = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),[Date]=EOMONTH(MAX('Table'[Date]),-1)))

    The final show:

    so if you want to calculate diff, you could add sum([value])-measure.


    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.