Average the same months over filtered years

[jeraldine]

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 date table.

What I want to achieve is this:

If slicer is from January 2019 to February 2021

February 2021 = Average (February 2021 MoM change, February 2020 MoM change, February 2019 MoM change)

March 2020 = Average(March 2020 MoM change, March 2019 MoM change)

Thanks in advance.

[jeraldine]

Here's the formula I'm working with:

QuoteValue MoM% Ave3 =

var theLastMonthIndex = CALCULATE(MAX(DateTable[month running index]))

var theLastFourYears =

SUMMARIZE(

SELECTCOLUMNS(

FILTER(

ALL(DateTable)

,DateTable[month running index] > theLastMonthIndex - 48 && DateTable[month running index] <= theLastMonthIndex

)

,"MonthIndex"

,DateTable[month running index]

)

,[MonthIndex]

)

return

CALCULATE(

AVERAGEX(

theLastFourYears

,calculate([QuoteValue MoM%])

)

,ALL('DateTable')

,theLastFourYears, month(DateTable[Date]))

[v-deddai1-msft]

Hi @jeraldine ,

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

Best Regards,

Dedmon Dai

[jeraldine]

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.

[v-deddai1-msft]

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]

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 · Accepted solution]

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.

[jeraldine]

Hi @Jihwan_Kim ,

It worked! Thanks so much for your help on this!

[Jihwan_Kim]

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]

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

[Jihwan_Kim]

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!!