topic Re: Trailing 6 months average in DAX Commands and Tips

Trailing 6 months average

PB_MZ — Wed, 15 Mar 2023 19:57:29 GMT

Hi,

I would like to calculate the trailing 6 month average for Margin % (A), Margin % (B), Margin % (C). They are measures instead of columns.

I tried several ways but none of them worked for me. I am wondering if I can find some luck here. Thank you in advance.

Re: Trailing 6 months average

FreemanZ — Wed, 15 Mar 2023 21:51:22 GMT

hi PB_MZ

what do you mean by "trailing 6 month average"?

Re: Trailing 6 months average

Anonymous — Tue, 21 Mar 2023 08:49:26 GMT

Hi PB_MZ ,

I think you can refer to below code to create measures.

Trailing 6 months average for Margin % (A) = VAR _EndDate = MAX ( DimDate[Date] ) VAR _StartDate = EOMONTH ( _EndDate, -7 ) + 1 RETURN AVERAGEX ( FILTER ( ALL ( DimDate ), DimDate[Date] >= _StartDate && DimDate[Date] <= _EndDate ), [Margin % (A)] )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Trailing 6 months average

PB_MZ — Tue, 21 Mar 2023 13:54:39 GMT

Hi,

Sorry for the late resposne. Trailing 6 months average means I need to calculate the average of (previous 5 months + current month) for each month.

Re: Trailing 6 months average

PB_MZ — Tue, 21 Mar 2023 14:40:52 GMT

Hi,

Thank you for your response. But unforturnately, I didn't get the results I want by your way. Correct me if I was wrong.

This is the code:

And this is the result from your code:

Re: Trailing 6 months average

PB_MZ — Tue, 21 Mar 2023 14:41:58 GMT

This is the results I would like to get.