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
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!