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.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, jeraldine 

Please correct me if I wrongly understood.

I think in this case, you can use VAR inside your measure to create a virtual table, and then you can simply AVERAGEX that virtual table.

If you have a sample data or a sample pbix file, and if it is OK to share, then I can have a look and try to write a DAX measure.

 

Thank you.

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!