Hello, I am trying to calculate my 8 month average (Jan-Aug) to populate for remainder of year (Sep-Dec)Can anyone help?MonthActualsAverageTotalJan148 1440Feb222 1440Mar120 1440Apr39 1440May94 1440Jun73 1440Jul85 1440Aug179 1440Sep 1201440Oct 1201440Nov 1201440Dec 1201440 Thanks for the help!!!Lori

yaya1974

Helper III

2 years ago

Solved

8 month average new column

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

Need Help

Irwan

2 years ago

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.

10 Replies

muhammad_786_1
Solution Supplier
2 years ago
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
  Helper III
  2 years ago
  Hello. Thank you for the help. I cannot access your file, my company blocked it. Can you send pbix file?
yaya1974
Helper III
2 years ago
Oh wait, plus filter on customer, year, model, finish
Thank you!
Irwan
Super User
2 years ago
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
  Helper III
  2 years ago
  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!
  - Irwan
    Super User
    2 years ago
    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.