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.

yaya1974's avatar
yaya1974
Icon for Helper III rankHelper III
2 years ago

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

Thank you!