Line Chart Legend Rolling Average

[vjr37]

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]

    )

[Anonymous · Accepted solution]

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.

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.

[Jihwan_Kim · Accepted solution]

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.

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] ) ) )
    )

[vjr37]

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

[Anonymous · Accepted solution]

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.

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.

[vjr37]

Thanks! This worked!