Forum Discussion

jeraldine's avatar
jeraldine
Frequent Visitor
5 years ago
Solved

Average the same months over filtered years

I need to get the average of a month-over-month measure for the same months over the years based on years in the date slicer. I already have a measure for the Month over Month change and I have a dat...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, jeraldine 

    Thank you very much for sharing the link.

    I am not very familiar with using a non-assigned-custom-date-table, so I was struggling a little.

    Please kindly check the below measure.

     

    MoM Avg by year-samemonth =
    VAR currentmonth =
    MAX ( DateTable[Date].[Month] )
    VAR newtable =
    FILTER (
    ADDCOLUMNS (
    SUMMARIZE ( ALL ( DateTable ), DateTable[Date].[Year], DateTable[Date].[Month] ),
    "@momratio", [QuoteValue MoM%]
    ),
    DateTable[Date].[Month] = currentmonth
    )
    RETURN
    IF (
    NOT ISBLANK ( [QuoteValueTotal] ),
    AVERAGEX ( newtable, [@momratio] ),
    BLANK ()
    )

     

    Thank you.

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi jeraldine ,

 

Would you please inform us more detailed information( your

 data and expected output (by OneDrive for Business)) if possible? Then we will help you more correctly.

 

Please do mask sensitive data before uploading.

 

Thanks for your understanding and support.

 

Best Regards,

Dedmon Dai