Forum Discussion
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 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?
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
13 Replies
Hi,
can you provide some input data and expected output.
Thanks,
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!
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.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?
Here is example.
