Forum Discussion
Helper III8 month average new column
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.
Super Userhello yaya1974
is this what you are looking for?
create a calculated column with following DAX.
Average =
var _Num = MONTH(CONVERT("2024-"&'Table'[Month]&"-1",DATETIME))
Return
IF(
_Num>8,
AVERAGEX(ALL('Table'),'Table'[Actuals]),
BLANK()
)
Helper IIIHi. Thank you. That works, however, I have multiple years, so having a formula looking at just a specific year does not work. I need a formula that will filter on the year and the customer and the model.
Appreciate your help!
- Irwan
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
I got it!!! YAYYYYY finally 🙂 Thank you 😀
- yaya1974
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!
- yaya1974
Here is excel example simplified:
Month Year Customer Model Finish Actuals (Jan-Aug) Average (Sep-Dec) Jan 2023 A T S 148 Feb 2023 B T AC 222 Mar 2023 A T AC 120 Apr 2023 A T AC 39 May 2023 B U S 94 Jun 2023 B U S 73 Jul 2023 A U S 85 Aug 2023 B U AC 17 Sep 2023 A T AC 127 Oct 2023 A T AC 127 Nov 2023 A T AC 127 Dec 2023 A T AC 127 Jan 2024 A U S 153 Feb 2024 B U S 227 Mar 2024 A U S 125 Apr 2024 A U AC 44 May 2024 B T S 99 Jun 2024 B T S 78 Jul 2024 A T S 90 Aug 2024 B T AC 22 Sep 2024 B U S 168 Oct 2024 B U S 168 Nov 2024 B U S 168 Dec 2024 B U S 168
- Ashish_Mathur
Hi,
Share data for multiple years and show the expected result. Share data in a format that can be pasted in an MS Excel file.
