Forum Discussion

yaya1974's avatar
yaya1974
Icon for Helper III rankHelper 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   144...
Need Help
  • Irwan's avatar
    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.

muhammad_786_1's avatar
muhammad_786_1
Icon for Solution Supplier rankSolution 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's avatar
yaya1974
Icon for Helper III rankHelper III
2 years ago

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