Forum Discussion

AA622's avatar
AA622
Helper II
3 years ago
Solved

Rolling yearly Average

Hello   I am trying to calculate a rolling average. Its to calculate the yearly usage of a certain product over the last rolling 12 months and then from there I will calculate the daily average by ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi AA622 ,

    Please update the formula of your measure [UsageRolling6mo] as below and check if it works or not...

    UsageRolling6mo =
VAR __MaxDate =
    TODAY ()
VAR __EOM12 =
    EOMONTH ( __MaxDate, -12 )
VAR __MinDate =
    DATE ( YEAR ( __EOM12 ), MONTH ( __EOM12 ), DAY ( __MaxDate ) )
VAR __Table =
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= __MaxDate
            && 'Calendar'[Date] >= __MinDate
    )
RETURN
    AVERAGEX ( __Table, [Usage] )

    In addition, you can refer the following links to get the rolling average values:

    Rolling Averages In Power BI

    Rolling 12 Months Average in DAX

    Best Regards

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

AA622 Try:

 

Better RA =
    VAR __MaxDate = TODAY() 
    VAR __EOM12 = EOMONTH(__MaxDate,-12)
    VAR __MinDate = DATE(YEAR(__EOM12),MONTH(__EOM12),DAY(__MaxDate)
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __MaxDate && [Date] >= __MinDate)
RETURN
    AVERAGEX(__Table,[Usage])

 

Both of the formulas you have currently are equally bad in my opinion because they do not account for leap years.

  • AA622's avatar
    AA622
    Helper II
    3 years ago
    Better RA =
    VAR __MaxDate = TODAY() 
    VAR __EOM12 = EOMONTH(__MaxDate,-12)
    VAR __MinDate = DATE(YEAR(__EOM12),MONTH(__EOM12),DAY(__MaxDate)
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __MaxDate && [Date] >= __MinDate)
RETURN
    AVERAGEX(__Table,[Usage])

     

    HAving issues with the last VAR line. Line 5

     

    Am I supposed to reference a table?

     

    I tried referencing our Calendar Table. What would I put at the last line? I also seem to be having an issue with the mindate VAR in line 5

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi AA622 ,

      Please update the formula of your measure [UsageRolling6mo] as below and check if it works or not...

      UsageRolling6mo =
VAR __MaxDate =
    TODAY ()
VAR __EOM12 =
    EOMONTH ( __MaxDate, -12 )
VAR __MinDate =
    DATE ( YEAR ( __EOM12 ), MONTH ( __EOM12 ), DAY ( __MaxDate ) )
VAR __Table =
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= __MaxDate
            && 'Calendar'[Date] >= __MinDate
    )
RETURN
    AVERAGEX ( __Table, [Usage] )

      In addition, you can refer the following links to get the rolling average values:

      Rolling Averages In Power BI

      Rolling 12 Months Average in DAX

      Best Regards