Forum Discussion
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...
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.
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.
I got it!!! YAYYYYY finally 🙂 Thank you 😀