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
    1 year 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
1 year 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

Here is excel example simplified:

MonthYearCustomerModelFinishActuals (Jan-Aug)Average (Sep-Dec)
Jan2023ATS148 
Feb2023BTAC222 
Mar2023ATAC120 
Apr2023ATAC39 
May2023BUS94 
Jun2023BUS73 
Jul2023AUS85 
Aug2023BUAC17 
Sep2023ATAC 127
Oct2023ATAC 127
Nov2023ATAC 127
Dec2023ATAC 127
Jan2024AUS153 
Feb2024BUS227 
Mar2024AUS125 
Apr2024AUAC44 
May2024BTS99 
Jun2024BTS78 
Jul2024ATS90 
Aug2024BTAC22 
Sep2024BUS 168
Oct2024BUS 168
Nov2024BUS 168
Dec2024BUS 168