cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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)

1 ACCEPTED SOLUTION
Super User

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 (
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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

10 REPLIES 10
Frequent Visitor

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]))
Community Support

Hi @jeraldine ,

Best Regards,

Dedmon Dai

Frequent Visitor

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.

Community Support

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

Frequent Visitor

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.

Super User

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 (
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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Hi @Jihwan_Kim ,

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

Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Community Support

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.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors