Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
86 | |
46 | |
28 | |
22 |