cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Average Last 6 months accumulated

Hello dear community, I come to you so you can help me with this DAX calculation.

I need a Calculated Column that shows me:

  • the average of the last 6 months accumulated for each collaborator (in case the collaborator appears > = 6 times in the year);
  • or in case the collaborator in the period of the current year appears less than 6 months, which shows the average based on the number of months that the collaborator has been in the company in the current period, for example:

Filtered Year: 2021
Current month: May2021 (5)
Age of the collaborator to May2021: 3 months (that is, the collaborator appears 3 times until May, if in June he becomes active, the collaborator when we are in June then will appear 4 times, and so on)

Then the average will be = (Sea + Apr + May)/3

Also consider, I already have a calendar table in my Power BI.

Here I have an example sample of what exactly I want (column "Average Last 6 months), you can download the file here:

I also leave the estracto here:

Reyesnes_0-1623906208099.png

I read them, I hope you can help me as soon as possible. and thank you very much in advance.

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@Syndicate_Admin 

Please create the following measure:

Measure = CALCULATE(AVERAGE([Compensation]),FILTER(ALLSELECTED('Table'),[Date]<=MAX([Date])),DATESINPERIOD('Table'[Date],MAX([Date]),-6,MONTH))
 

Vpazhenmsft_1-1624263566775.png

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
V-pazhen-msft
Community Support
Community Support

@Syndicate_Admin 

Please create the following measure:

Measure = CALCULATE(AVERAGE([Compensation]),FILTER(ALLSELECTED('Table'),[Date]<=MAX([Date])),DATESINPERIOD('Table'[Date],MAX([Date]),-6,MONTH))
 

Vpazhenmsft_1-1624263566775.png

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

amitchandak
Super User
Super User

@Syndicate_Admin , Try a measure with date table, that would be better

 

Rolling 6= CALCULATE(Average(Table[compensation]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

 

or

 

Rolling 6= CALCULATE(AverageX(values('Date'[Month year]),calculate(sum(Table[compensation]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.