Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello guys,
I'm trying to display an evolution rate which compares datas over 2 years. BUT, I only want common months to be part of the calculation. (In 2022, I have datas for 6 months, and in 20221 I have datas for 5 months). I just want my measure to calculate the rate between the 5 common months.
Here is an extract of my data.
And here is my DAX measure :
Rate N /N-1 =
divide ( sum('file'[Data N])-sum('file'[Data N-1]),sum('file'[Data N-1]),"NA")
Of course, I want to be able do display my charts for each year, using a slicer for years.
Can someone give me the solution please ? I'm sure it's not that difficult !
Thans a lot !
Solved! Go to Solution.
Hi @Jackisover ,
You can try this method:
For the 04/01/2022, you can do this:
MeasureN = CALCULATE(SUM(file[Data N]), FILTER('file','file'[Date] <> DATE(2022,4,1)))
MeasureN-1 = CALCULATE(SUM(file[Data N-1]), FILTER('file', 'file'[Date N-1] <> MIN('file'[Date N-1])))
Result = DIVIDE([MeasureN] - [MeasureN-1], [MeasureN-1])
The result is:
Hope this helps you.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Jackisover ,
I use your data and DAX to have a try.
And then I create a column:
Date N-1 = 'file'[Date] - 365
Then I make a chart like this:
Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yinliw-msft and thanks a lot for your work.
The rate is correct, but what happenned to my data for 01/04/2022 ? I need it to be seen on my dashboard, and it seems it's not in your file. Check out my screenshot :
Also I dont understand the importance of a "date n-1"
Thank you for your help
Hi @Jackisover ,
Because I looked your sample data that I think the April seems not one of the five common months?
Could you please explain to me what calculation do you want for the 01/04/2022?
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Indeed, april is the problematic month because I don't have any data in april 2021. But I still wish to display datas for april 2022.
My charts and slicers are OK : if i select 2022 i see from april to september, and if i select 2021, i see from may to september.
But my rate is wrong. It displays "18%" instead of "8%" because april 2022 is integrated to the calculation. I'd like the rate to calculate only the 5 common months but still keep my datas for other charts .
🆙
If someone can help me find a solution...
Thanks !
Hi @Jackisover ,
You can try this method:
For the 04/01/2022, you can do this:
MeasureN = CALCULATE(SUM(file[Data N]), FILTER('file','file'[Date] <> DATE(2022,4,1)))
MeasureN-1 = CALCULATE(SUM(file[Data N-1]), FILTER('file', 'file'[Date N-1] <> MIN('file'[Date N-1])))
Result = DIVIDE([MeasureN] - [MeasureN-1], [MeasureN-1])
The result is:
Hope this helps you.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yinliw-msft
Thank you for your help, this is a good solution.
Actually, I thought there would be a way to automatize the calculation of a rate based on common months, but since there is not (maybe there is ?), the best way is to create a new column or a measure to reproduce common months only.
Thanks,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |