March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello All,
i have a question that includes 2 parts :
1. i need please 2 measures ; one return SUM of values of Previous Month this Year, and the second Previous Month Previous year and this needs to be filterd automatically by the Date Field (Month,Year) withiout having me filtering this manually in the filters bar on the right. Each measrue will be dragged in each visula as the below screenshot. As you can see below i created the Sum of Items and dragged it into the Values box, so this feild needs to be used in both measures .
2. The second part, i need the visual Title to be changed automatically based on the above measures, so if we are in August the title needs to be July 2022 on th right visual and July 2021 on the left visual.
thank you,
Solved! Go to Solution.
you create three calculated columns one to get the month name and the second to get the year and one for month year.
so you will be able to slice on these two columns.
after the user select the month and year the blow measures will works well for you:
Previous month last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-13,MONTH)))
Previous month this year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
and the below two measures are for the month name
previous Month Name this year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
previous Month Name previous year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-13,MONTH)))
so you can append the last two measures to text box .
please let me know if it solved your needs.
The below measure seems not to be implementing correctly as it gave me December 2022 while it show show September 2022 as the current month.
previous Month Name this year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
you create three calculated columns one to get the month name and the second to get the year and one for month year.
so you will be able to slice on these two columns.
after the user select the month and year the blow measures will works well for you:
Previous month last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-13,MONTH)))
Previous month this year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
and the below two measures are for the month name
previous Month Name this year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
previous Month Name previous year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-13,MONTH)))
so you can append the last two measures to text box .
please let me know if it solved your needs.
Thank indeed Ahmed,
the results came just as excepted only after i changed the date field to be freom the Calendar table. previous Month Name this year = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
and everything else worked too.
thank you indeed , i will try them out and let your know.
Thanks for the rapid response
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
25 | |
18 | |
15 | |
9 | |
8 |
User | Count |
---|---|
37 | |
32 | |
21 | |
16 | |
14 |