Forum Discussion
Solved
Need formula help
I need to average 6 months of data for a spefic data range in a new column. For example even though it is June, I need to average 6 months of data from Nov '23 - Apr 2024 Jan - June will be the sam...
Hi yaya1974 ,
The Table data is shown below:
Try this.
Avarage = VAR _a = [Date] VAR _table1 = ADDCOLUMNS('Table', "MonthNumber",SWITCH(TRUE(), LEFT([Date],3) = "Jan",1, LEFT([Date],3) = "Feb",2, LEFT([Date],3) = "Mar",3, LEFT([Date],3) = "Apr",4, LEFT([Date],3) = "May",5, LEFT([Date],3) = "Jun",6, LEFT([Date],3) = "Jul",7, LEFT([Date],3) = "Aug",8, LEFT([Date],3) = "Sep",9, LEFT([Date],3) = "Oct",10, LEFT([Date],3) = "Nov",11, LEFT([Date],3) = "Dec",12 ) ) VAR _table2 = ADDCOLUMNS(_table1, "Rank",RIGHT([Date],2) * 100 + [MonthNumber]) VAR _table3 = ADDCOLUMNS(_table2, "Begin",IF( [MonthNumber] >=1 && [MonthNumber] <=6, (RIGHT([Date],2) - 1) * 100 + 5, (RIGHT([Date],2) - 1) * 100 + 11), "End",IF( [MonthNumber] >=1 && [MonthNumber] <=6, (RIGHT([Date],2) - 1) * 100 + 10, RIGHT([Date],2) * 100 + 4) ) VAR _table4 = ADDCOLUMNS(_table3,"Average",AVERAGEX(FILTER(_table3,[Rank] >= EARLIER([Begin]) && [Rank] <= EARLIER([End])),[Input])) RETURN MAXX(FILTER(_table4,[Date] = _a),[Average])Final output
Hi yaya1974 ,
I apologize for not seeing your message until now, are you trying to calculate a three month average the way I marked it?
Try this
Column =
VAR _a = [Date]
VAR _table1 =
ADDCOLUMNS('Table',
"MonthNumber",SWITCH(TRUE(),
LEFT([Date],3) = "Jan",1,
LEFT([Date],3) = "Feb",2,
LEFT([Date],3) = "Mar",3,
LEFT([Date],3) = "Apr",4,
LEFT([Date],3) = "May",5,
LEFT([Date],3) = "Jun",6,
LEFT([Date],3) = "Jul",7,
LEFT([Date],3) = "Aug",8,
LEFT([Date],3) = "Sep",9,
LEFT([Date],3) = "Oct",10,
LEFT([Date],3) = "Nov",11,
LEFT([Date],3) = "Dec",12
)
)
VAR _table2 = ADDCOLUMNS(_table1,
"Rank",RIGHT([Date],2) * 100 + [MonthNumber])
VAR _table3 = ADDCOLUMNS(_table2,
"Begin",
SWITCH(TRUE(),
[MonthNumber] >= 1 && [MonthNumber] <= 3,(RIGHT([Date],2) - 1) * 100 + 5 ,
[MonthNumber] >= 4 && [MonthNumber] <= 6,(RIGHT([Date],2) - 1) * 100 + 8 ,
[MonthNumber] >= 7 && [MonthNumber] <= 9,(RIGHT([Date],2) - 1) * 100 + 11 ,
RIGHT([Date],2) * 100 + 2
)
,
"End",
SWITCH(TRUE(),
[MonthNumber] >= 1 && [MonthNumber] <= 3,(RIGHT([Date],2) - 1) * 100 + 7 ,
[MonthNumber] >= 4 && [MonthNumber] <= 6,(RIGHT([Date],2) - 1) * 100 + 10 ,
[MonthNumber] >= 7 && [MonthNumber] <= 9,RIGHT([Date],2) * 100 + 1 ,
RIGHT([Date],2) * 100 + 4
)
)
VAR _table4 = ADDCOLUMNS(_table3,"Average",AVERAGEX(FILTER(_table3,[Rank] >= EARLIER([Begin]) && [Rank] <= EARLIER([End])),[Value]))
RETURN MAXX(FILTER(_table4,[Date] = _a),[Average])
If I have misunderstood, please provide simple data and show the expected results as a picture.
Best Regards,
Wenbin Zhou
Sorry to keep bothering. the formula works, just cannot get the results for all 4 quarters to match. Can you help still? Here is the pic you asked for.
Appreciate your help!
Oh and this is what I changed to get results, it works but missing a quarter..............hmmmm
Its missing Q2 and Q1 is incorrect. I know its probably a simple change and I will keep trying to get it right. Any help is great! Thank you!