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.

Irwan's avatar
Irwan
Icon for Super User rankSuper 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.

yaya1974's avatar
yaya1974
Icon for Helper III rankHelper III
1 year ago

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!