8 month average new column

[yaya1974]

Hello,  I am trying to calculate my 8 month average (Jan-Aug) to populate for remainder of year (Sep-Dec)

Can anyone help?

Month	Actuals	Average	Total
Jan	148		1440
Feb	222		1440
Mar	120		1440
Apr	39		1440
May	94		1440
Jun	73		1440
Jul	85		1440
Aug	179		1440
Sep		120	1440
Oct		120	1440
Nov		120	1440
Dec		120	1440

Thanks for the help!!!

Lori

[muhammad_786_1]

You can handle the missing data for the remainder of the year (Sep-Dec) by using this M code:

 

JanToAug = Table.SelectRows(#"Changed Type", each [MonthNo] >= 1 and [MonthNo] <= 8),
AvgActuals = List.Average(List.RemoveNulls(JanToAug[Actuals])),
AddNewCol = Table.AddColumn(#"Changed Type", "NewCol", each if [Actuals] = null then AvgActuals else [Actuals], Int64.Type)

I've also attached a link to a reference file that you can check for more details

File

Best Regards,
Muhammad Yousaf

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

LinkedIn

[yaya1974]

Hello.  Thank you for the help.  I cannot access your file, my company blocked it.  Can you send pbix file?

[Irwan]

hello @yaya1974 

is this what you are looking for?

create a calculated column with following DAX.

Average =
var _Num = MONTH(CONVERT("2024-"&'Table'[Month]&"-1",DATETIME))
Return
IF(
    _Num>8,
    AVERAGEX(ALL('Table'),'Table'[Actuals]),
    BLANK()
)

Hope this will help.

Thank you.

[yaya1974]

Hi.  Thank you.   That works, however, I have multiple years, so having a formula looking at just a specific year does not work.  I need a formula  that will filter on the year and the customer and the model.

Appreciate your help!

[Ashish_Mathur]

Hi,

Share data for multiple years and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Irwan · Accepted solution]

hello @yaya1974 

i dont know how your year value in your data, but you can simply add year value as filter.

Average =
var _Num = MONTH(CONVERT("2024-"&'Table'[Month]&"-1",DATETIME))
Return
IF(
    _Num>8&&VALUE('Table'[Column1])=YEAR(TODAY()),
    AVERAGEX(FILTER(ALL('Table'),VALUE('Table'[Column1])=YEAR(TODAY())),'Table'[Actuals]),
    BLANK()
)

as you can see, add in if statement and you can do average on all data before September in on-going year.

But if you want to calculate each year, you can add filter in AVERAGEX

Average = 
var _Num = MONTH(CONVERT("2024-"&'Table'[Month]&"-1",DATETIME))
Return
IF(
    _Num>8,
    AVERAGEX(FILTER(ALL('Table'),VALUE('Table'[Column1])=YEAR(TODAY())),'Table'[Actuals]),
    BLANK()
)

Hope this will help.

Thank you.

[yaya1974]

I got it!!! YAYYYYY   finally 🙂   Thank you 😀

[yaya1974]

Here is excel example simplified:

Month	Year	Customer	Model	Finish	Actuals (Jan-Aug)	Average (Sep-Dec)
Jan	2023	A	T	S	148
Feb	2023	B	T	AC	222
Mar	2023	A	T	AC	120
Apr	2023	A	T	AC	39
May	2023	B	U	S	94
Jun	2023	B	U	S	73
Jul	2023	A	U	S	85
Aug	2023	B	U	AC	17
Sep	2023	A	T	AC		127
Oct	2023	A	T	AC		127
Nov	2023	A	T	AC		127
Dec	2023	A	T	AC		127
Jan	2024	A	U	S	153
Feb	2024	B	U	S	227
Mar	2024	A	U	S	125
Apr	2024	A	U	AC	44
May	2024	B	T	S	99
Jun	2024	B	T	S	78
Jul	2024	A	T	S	90
Aug	2024	B	T	AC	22
Sep	2024	B	U	S		168
Oct	2024	B	U	S		168
Nov	2024	B	U	S		168
Dec	2024	B	U	S		168

[yaya1974]

ok but I need it filtered even farther down.  Not sure by year, but by customer and model and finish.  averagex only allows one filter.   the actuals are different for different years, but your formula above is only giving me the same answer for all the years.   I will put in excel data I am looking for and hopefully you can help me?!!   Thank you!

[yaya1974]

Oh wait,  plus filter on customer, year, model, finish

Thank you!