Forum Discussion
Need formula help
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 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.
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
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?