Forum Discussion

rogerdea's avatar
rogerdea
Icon for Helper IV rankHelper IV
6 years ago
Solved

Rolling 3 Year Average

Hello   I am trying to figure out how to calculate this from my data and struggling with this.  I need the rolling 3 year average of the sum of units, taking into account that for the first year th...
Need Help
Tutorial Requests
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi rogerdea ,

    Please try like this:

    • Create a calendar table.

     

    Date =
VAR _calendar =
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )
RETURN
    ADDCOLUMNS ( _calendar, "Year", YEAR ( [Date] ) )

     

    • Create a measure.

     

    Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Unit] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, YEAR )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Date'[Year] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, YEAR )
    )
)

     

    For more details, please see the attachment.

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-xuding-msft's avatar
v-xuding-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi rogerdea ,

Please try like this:

  • Create a calendar table.

 

Date =
VAR _calendar =
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )
RETURN
    ADDCOLUMNS ( _calendar, "Year", YEAR ( [Date] ) )

 

  • Create a measure.

 

Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Unit] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, YEAR )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Date'[Year] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, YEAR )
    )
)

 

For more details, please see the attachment.

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • rogerdea's avatar
    rogerdea
    Icon for Helper IV rankHelper IV
    6 years ago

    Thanks v-xuding-msft 

     

    I'm getting an error half way through the second part you posted: (blurred my table names for privacy)

    Have i done something wrong?

     

    • v-xuding-msft's avatar
      v-xuding-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi rogerdea ,

      There is an extra parenthesis in your formula. I point it out with red pen. Please remove it to have a try.

       

      You could download the pbix file from my answer above. It may help you a little.

      • rogerdea's avatar
        rogerdea
        Icon for Helper IV rankHelper IV
        6 years ago

        v-xuding-msft 

         

        amazing this works in my main pbx file now.  However one thing which doesnt look right on your attached pbx is the 2019 average is incorrect. All others are correct.  Using the same DAX this does work in my file though.