cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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)

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

1 ACCEPTED SOLUTION
Community Support

Hi @yaya1974 ,

The Table data is shown below:

Try this.

``````Avarage =
VAR _a = [Date]
VAR _table1 =
"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
)
)
"Rank",RIGHT([Date],2) * 100 + [MonthNumber])
"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 13
Community Support

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 =
"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
)
)
"Rank",RIGHT([Date],2) * 100 + [MonthNumber])
"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.

Helper III

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?

Helper III

Here is example.

Community Support

Hi @yaya1974 ,

The Table data is shown below:

Try this.

``````Avarage =
VAR _a = [Date]
VAR _table1 =
"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
)
)
"Rank",RIGHT([Date],2) * 100 + [MonthNumber])
"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

Helper III

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

Thank you!

Community Support

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 =
"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
)
)
"Rank",RIGHT([Date],2) * 100 + [MonthNumber])
"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

Helper III

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.

Helper III

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!

Helper III

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

Helper III

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.

Thank you!

Helper III

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?

Resolver I

Hi,

can you provide some input data and expected output.

Thanks,

Helper III

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors