Need formula help

[yaya1974]

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 same past 6 month average, then July - Dec 2024 will start the average of the next 6 months (May - Oct)

I prefer not to use a measure, but an actual formula in a new column.  Can anyone help, please?

[v-zhouwen-msft]

Hi @Ankur04 ,thanks for your quick reply, I will add more.

Hi @yaya1974 ,

If I understand correctly, January to June 2024 are calculated as the average of November 2023 to April 2024.

The Table data is shown below:(The data type of the 'Date' column is Text)

Use the following DAX expression to create a column.

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 + 11,
RIGHT([Date],2) * 100 + 5),
"End",IF( [MonthNumber] >=1 && [MonthNumber] <=6,
RIGHT([Date],2) * 100 + 4,
RIGHT([Date],2) * 100 + 10)
)
VAR _table4 = ADDCOLUMNS(_table3,"Average",AVERAGEX(FILTER(_table3,[Rank] >= EARLIER([Begin]) && [Rank] <= EARLIER([End])),[Value]))
RETURN MAXX(FILTER(_table4,[Date] = _a),[Average])

Final output   0.425 = (0.4375 + 0.4375 + 0.4125 + 0.4125 +0.4125 + 0.4375 ) / 6

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaya1974]

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]

Here is example.

 

[v-zhouwen-msft · Accepted solution]

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]

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

Thank you!

[v-zhouwen-msft]

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

[yaya1974]

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!

[yaya1974]

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!

[yaya1974]

Yes that is what I am looking for.  So I just change the numbers to match the months I need calculated, correct?  Like begins at 3 ends at 5, begins at 6 ends at 8, begins at 9 ends at 11, begins at 12, ends at 2.

I reallly appreciate your help!!  Thanks so much

[yaya1974]

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]

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?

[Ankur04]

Hi,

can you provide some input data and expected output.

Thanks,

[yaya1974]

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!