Forum Discussion

yaya1974's avatar
yaya1974
Helper III
2 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

Ankur04's avatar
Ankur04
Resolver II
2 years ago

Hi,

 

can you provide some input data and expected output.

 

Thanks,

yaya1974's avatar
yaya1974
Helper III
2 years ago

Yes, I did.   the input data is the first set of numbers.  If you take Nov thru April 2023, starting in Jan you get the average you see in the second set of numbers and that stays the same through June, then starting in July you get the next 6 month average  May - Oct. and so on.   Does this make sense?   

Thank you!