Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PB_MZ
Regular Visitor

Trailing 6 months average

Hi,

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

PB_MZ_0-1678909787493.png

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.

5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

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.

PB_MZ_2-1679409669283.png

This is the results I would like to get. 

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:

PB_MZ_0-1679409361064.png

And this is the result from your code:

PB_MZ_1-1679409396258.png

 

 

FreemanZ
Super User
Super User

hi @PB_MZ 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors