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 ,

 

Lack of information, we can't help you well.

 

Best Regards,

Dedmon Dai

 

jeraldine's avatar
jeraldine
Frequent Visitor
5 years ago

Sorry about this. This is my 1st post and I'm getting frustrated with this measure.

 

Here's an example of what my data looks like and what I want to get from my measure.

Power BI dataExpected output

 

MoM% is a measure of the current month sales vs. previous month sales. What I want to do is to get the average MoM% by month based on the 4 years' MoM% for that month. My slicer is from April 1, 2017 to February 28, 2021 for this data.

 

For January, average of 2018, 2019, 2020 and 2021 MoM% is 128.76%. For March, since we don't have the data yet for 2021, it only has 3 data points, it should say 103.21%.

 

I tried to use Dateadd to get MoM% each year, total it and divide by the number of datapoints, but since the datapoints are not the same for each month, I'm getting the wrong number for some months

 

I hope someone can help. Thanks.

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

    Hi, jeraldine 

    If you are looking for the outcome like below picture, please try the below measure.

     

    Avg MoM% by Month =
    AVERAGEX('Table', 'Table'[MoM%])
     

    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 ,

     

    Just based on your sample data, you can try the following measure:

     

    QuoteValue MoM% Ave3 = AVERAGEX(FILTER(ALLSELECTED('Table'),Table[Month] = MAX(Table[Month])),[MOM%])

     

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

     

    Best Regards,

    Dedmon Dai

    • jeraldine's avatar
      jeraldine
      Frequent Visitor
      5 years ago

      Hi v-deddai1-msft, Jihwan_Kim ,

       

      Thanks for your suggestions but in both suggested measures, the result is the MoM% for the month and not the average for the 3 or 4 data points of several years.

       

      I've loaded a sample pbix file in this link. 

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        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.