Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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:
Solved! Go to Solution.
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.
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.
Works perfectly. Thank you
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.
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?
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.
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
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))
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 , 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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
55 | |
54 | |
37 | |
29 |
User | Count |
---|---|
78 | |
62 | |
45 | |
40 | |
40 |