topic Re: Evolution rate only for common months in DAX Commands and Tips

Evolution rate only for common months

Jackisover — Wed, 16 Nov 2022 08:28:40 GMT

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 !

Re: Evolution rate only for common months

v-yinliw-msft — Thu, 17 Nov 2022 07:55:18 GMT

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.

Re: Evolution rate only for common months

Jackisover — Thu, 17 Nov 2022 08:12:10 GMT

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

Re: Evolution rate only for common months

v-yinliw-msft — Thu, 17 Nov 2022 08:17:45 GMT

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.

Re: Evolution rate only for common months

Jackisover — Thu, 17 Nov 2022 08:54:25 GMT

v-yinliw-msft

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 .

Re: Evolution rate only for common months

Jackisover — Tue, 22 Nov 2022 07:59:08 GMT

🆙

If someone can help me find a solution...

Thanks !

Re: Evolution rate only for common months

v-yinliw-msft — Tue, 22 Nov 2022 08:54:39 GMT

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.

Re: Evolution rate only for common months

Jackisover — Tue, 22 Nov 2022 15:37:10 GMT

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,