Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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
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
One othe question. using the same formula what do i change to get the 3 month average?
Thank you!
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
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!
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!
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
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!
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?
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
30 | |
16 | |
14 | |
14 | |
9 |