Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
yaya1974
Helper III
Helper III

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)

yaya1974_1-1719247642858.png

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

1 ACCEPTED SOLUTION

Hi @yaya1974 ,

The Table data is shown below:

vzhouwenmsft_0-1719397160981.png

 

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

vzhouwenmsft_1-1719397198089.png

 

View solution in original post

13 REPLIES 13
v-zhouwen-msft
Community Support
Community Support

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.

vzhouwenmsft_0-1719295923403.png

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

vzhouwenmsft_1-1719300421039.png

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])

 

vzhouwenmsft_3-1719300560821.png

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

vzhouwenmsft_4-1719300589473.png

 

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.

Screenshot 2024-06-25 114002.png

Hi @yaya1974 ,

The Table data is shown below:

vzhouwenmsft_0-1719397160981.png

 

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

vzhouwenmsft_1-1719397198089.png

 

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?

vzhouwenmsft_0-1721639293137.png

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])

 

vzhouwenmsft_1-1721641239615.png

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.

 

yaya1974_0-1721741344800.png

 

Appreciate your help!

Oh and this is what I changed to get results,  it works but missing a quarter..............hmmmm

yaya1974_2-1721743919140.png

 

yaya1974_1-1721743884586.png

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?

yaya1974_0-1719416318838.png

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?

Ankur04
Resolver II
Resolver II

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors