cancel
Showing results for
Did you mean:

## 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: 2021Current 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:

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

1 ACCEPTED SOLUTION
Community Support

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

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

2 REPLIES 2
Community Support

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

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

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))

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!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!