cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reyesnes
Helper I
Helper I

Average Last 6 months accumulated

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

 

I need a Calculated Column to show me:

  • the average of the last 6 months accumulated for each collaborator (in case the collaborator appears> = 6 times in the year);
  • or in the event that the employee in the current year period appears less than 6 months, showing the average based on the number of months that the employee has been in the company in the current period, for example:
Year Filter: 2021
Current month: May2021 (5)
Seniority of the collaborator to May2021: 3 months (that is, the collaborator appears 3 times until May,
if in June it becomes active, the collaborator when we are in June then it will appear 4 times, and so on)

So the average will be = (Mar + Apr + May) / 3

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

 

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

I also leave the extract here:

Reyesnes_0-1623936664281.png

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

 

Nestor Reyes

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

AVG 6M = 
CALCULATE(
    AVERAGEX( DATA, DATA[Compensation] ),
    DATESINPERIOD( DATA[Date], MAX( DATA[Date] ), -6, MONTH )
)

Screenshot 2021-06-17 204335.png

View solution in original post

2 REPLIES 2
Reyesnes
Helper I
Helper I

Muchas gracias @CNENFRNL 

CNENFRNL
Community Champion
Community Champion

AVG 6M = 
CALCULATE(
    AVERAGEX( DATA, DATA[Compensation] ),
    DATESINPERIOD( DATA[Date], MAX( DATA[Date] ), -6, MONTH )
)

Screenshot 2021-06-17 204335.png

View solution in original post

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.