Forum Discussion

vjr37's avatar
vjr37
Regular Visitor
1 year ago
Solved

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]

 

    )

 

 

"Lines Chart"
Legends
Rolling Averages
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
    Jihwan_Kim
    1 year ago

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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      vjr37
      Regular Visitor
      1 year ago

      Thanks! This worked! 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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's avatar
      vjr37
      Regular Visitor
      1 year ago

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