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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
vjr37
Regular Visitor

Line Chart Legend Rolling Average

Have this table of data in power billing with thus measure. Issue I'm running into is when I create a line chart the rolling average is calculated correctly (dates on x axis rolling on your axis). If I add [Name] to legend the names A and B show up on the legend but there remains only 1 line with the total rolling average and not two lines with rolling average for both A and B. Any help would be appreciated 

 

 

 

 

 

RollingMean6Months = 

 

VAR CurrentDate = MAX('Charge'[Date])

 

VAR SixMonthsAgo = EDATE(CurrentDate, -6)

 

RETURN 

 

    AVERAGEX(

 

        FILTER(

 

            ALL('Charge'), 

 

            'Charge'[Date] <= CurrentDate && 'Charge'[Date] > SixMonthsAgo

 

        ), 

 

        'Charge'[Value]

 

    )

 

Screenshot_20241003_140610_Photos.jpg

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @vjr37 ,

 

If you want to add [Name] in Legend and display two lines based on Name, I suggest you to update code as below.

RollingMean6Months = 
VAR CurrentDate =
    MAX ( 'Charge'[Date] )
VAR SixMonthsAgo =
    EDATE ( CurrentDate, -6 )
RETURN
    AVERAGEX (
        FILTER (
            ALL( 'Charge'),
            Charge[Name] = MAX(Charge[Name]) &&
            'Charge'[Date] <= CurrentDate
                && 'Charge'[Date] > SixMonthsAgo
        ),
        'Charge'[Value]
    )

Result is as below.

vrzhoumsft_0-1728009743278.png

 

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.

 

View solution in original post

Jihwan_Kim
Super User
Super User

Hi,

One of ways is to have dimension tables for date and company, and create one to many relationship to the fact table, that looks something like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1728013276942.png

 

 

Jihwan_Kim_0-1728013262117.png

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

sales 6 months rolling avg: = 
VAR _t =
    WINDOW (
        -5,
        REL,
        0,
        REL,
        ALL ( calendar_dim[Year-Month], calendar_dim[Year-Month sort] ),
        ORDERBY ( calendar_dim[Year-Month sort], ASC )
    )
RETURN
    IF (
        SUM ( sales_fact[value] ),
        AVERAGEX ( _t, CALCULATE ( SUM ( sales_fact[value] ) ) )
    )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi,

One of ways is to have dimension tables for date and company, and create one to many relationship to the fact table, that looks something like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1728013276942.png

 

 

Jihwan_Kim_0-1728013262117.png

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

sales 6 months rolling avg: = 
VAR _t =
    WINDOW (
        -5,
        REL,
        0,
        REL,
        ALL ( calendar_dim[Year-Month], calendar_dim[Year-Month sort] ),
        ORDERBY ( calendar_dim[Year-Month sort], ASC )
    )
RETURN
    IF (
        SUM ( sales_fact[value] ),
        AVERAGEX ( _t, CALCULATE ( SUM ( sales_fact[value] ) ) )
    )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

This also worked as well. I'd say this method follows appropriate standard practice too. Thanks for the help

Anonymous
Not applicable

Hi @vjr37 ,

 

If you want to add [Name] in Legend and display two lines based on Name, I suggest you to update code as below.

RollingMean6Months = 
VAR CurrentDate =
    MAX ( 'Charge'[Date] )
VAR SixMonthsAgo =
    EDATE ( CurrentDate, -6 )
RETURN
    AVERAGEX (
        FILTER (
            ALL( 'Charge'),
            Charge[Name] = MAX(Charge[Name]) &&
            'Charge'[Date] <= CurrentDate
                && 'Charge'[Date] > SixMonthsAgo
        ),
        'Charge'[Value]
    )

Result is as below.

vrzhoumsft_0-1728009743278.png

 

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.

 

Thanks! This worked! 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.