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

     

yaya1974's avatar
yaya1974
Helper III
2 years ago

Hi Wenbin,  Thank you!   You Almost got it!   the formula works but it is just averaging the wrong data to get to the answer.  for example:

Jan-24 through Jun-24 = .4543

to get to that answer the average comes from May-23 through Oct-23

So, the data is six months in the past (going back two months to start)   hope that makes sense?

is there a way to edit formula so that can happen?

yaya1974's avatar
yaya1974
Helper III
2 years ago

Here is example.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

     

    • yaya1974's avatar
      yaya1974
      Helper III
      2 years ago

      Thank you!   Appreciate all your help!  This is a very important project I am working on, I may need more help.  If you don't mind?

    • yaya1974's avatar
      yaya1974
      Helper III
      2 years ago

      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!

       

    • yaya1974's avatar
      yaya1974
      Helper III
      2 years ago

      One othe question.  using the same formula what do i change to get the 3 month average?

      Thank you!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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