Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jeraldine
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)

 

Thanks in advance.

 

 

 

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

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.


Go to My LinkedIn Page


View solution in original post

10 REPLIES 10
jeraldine
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]))

Hi @jeraldine ,

 

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

 

Best Regards,

Dedmon Dai

 

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 dataPower BI dataExpected outputExpected 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.

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

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

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.

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.


Go to My LinkedIn Page


Hi @Jihwan_Kim ,

 

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

Hi, @jeraldine 

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

 

Picture1.png

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.


Go to My LinkedIn Page


v-deddai1-msft
Community Support
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.

 

Please do mask sensitive data before uploading.

 

Thanks for your understanding and support.

 

Best Regards,

Dedmon Dai

Jihwan_Kim
Super User
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.

 

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

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.


Go to My LinkedIn Page


Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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