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.

     

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.

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

      Hi rogerdea ,

      Glad it works fine in your sample.😊

       

      For the wrong result, it might be caused by date. The format of date is mm/dd/yyyy rather than dd/mm/yyyy in my sample. I don't change it to match your sample date completely. I just import it and the Desktop change it from your format to mm/dd/yyyy automatically.

       

      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.