Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
Sign up nowGet Fabric certified for FREE! Don't miss your chance! Learn more
Hi,
2 questions in one post. Board+Dataset attached
The CRM of my company provides me each month, in Excel format, a statement of active licenses of several products.
Ex:
id name productCode creationDate price
13 Customer 9 Product 000 16/11/2020 600
12 Customer 8 Product 001 12/11/2020 500
11 Customer 7 Product 001 12/11/2020 500
10 Customer 6 Product 000 14/09/2020 300
9 Customer 6 Product 001 14/09/2020 600
8 Customer 5 Product 001 31/08/2020 200
7 Customer 5 Product 001 31/08/2020 200
6 Customer 5 Product 000 31/08/2020 200
5 Customer 4 Product 001 17/10/2019 400
4 Customer 3 Product 001 12/03/2019 450
3 Customer 3 Product 000 08/03/2019 450
2 Customer 2 Product 001 04/01/2019 350
1 Customer 1 Product 001 11/12/2018 300
I aggregate them and I built a report with usefull KPI
Now I'm stucked on 2 issues
1 - How compute the difference of the values between the current month and the previous one ?
Ex: 1300 between May and April
I played with DAX functions but without success so far
2 - How to display on a table the news and the removed lines for the N lastest months?
ex: news deals (win) and the lost one, for the last 3 months
Status id Name Date Difference
Win 18 Customer 12 may-21 +600
Win 17 Customer 7 may-21 +500
Win 16 Customer 11 april-21 +500
Lost 12 Customer 8 april-21 -500
Win 15 Customer 4 march-21 +600
Lost 6 Customer 5 march-21 -200
Lost 2 Customer 2 march-21 -350
Lost 1 Customer 1 march-21 -300
Thanks
Solved! Go to Solution.
@Anonymous , we usually use time intelligence for that with date table
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
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
Hi @Anonymous ,
For the first case, you need to create a new date table and create the relationship between your fact table and the date table:
Table = ADDCOLUMNS(CALENDARAUTO(),"Monthyear",FORMAT([Date],"mmm")&"-"&FORMAT([Date],"yy"))
Then you can use the following measure:
Measure = var MTDSales = CALCULATE(SUM(powerBI[price]),DATESMTD('Table'[Date])) var lastMTDSales = CALCULATE(SUM(powerBI[price]),DATESMTD(DATEADD('Table'[Date],-1,MONTH))) return MTDSales-lastMTDSales
You can refer to the pbix file.
For your second case, I don't understand your logic , would you please explain more about it ?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Anonymous , we usually use time intelligence for that with date table
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
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
Thanks you solve my first case 🙂
But I believe there is a lack of efficiency on the way I implement it because I use 3 measure to get the result:
1 - MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
2 - last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
3 - Result = MTD Sales - last MTD Sales
No way to do this in only one step?
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2026 Power BI update to learn about new features.
| User | Count |
|---|---|
| 65 | |
| 65 | |
| 45 | |
| 21 | |
| 18 |
| User | Count |
|---|---|
| 115 | |
| 114 | |
| 38 | |
| 36 | |
| 26 |