Average Last 6 months accumulated

[Anonymous]

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:

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

Nestor Reyes

[Anonymous]

Muchas gracias @CNENFRNL 

[CNENFRNL · Accepted solution]

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