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 ,
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
Now I need a new column with an adjustment based off the output column with a 3% threshold. is this possible?
I tried a few calcs functions but not getting it. it won't subtract, only getting a number that exists in the output column.
I will keep trying. Please help if you can.
Thank you!