Forum Discussion
Anonymous
5 years agoNot applicable
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:
I read them, I hope they can help me as soon as possible. and thank you very much in advance.
Nestor Reyes
AVG 6M = CALCULATE( AVERAGEX( DATA, DATA[Compensation] ), DATESINPERIOD( DATA[Date], MAX( DATA[Date] ), -6, MONTH ) )