topic Re: Monthly Average of Employees YTD/Rolling12 in DAX Commands and Tips

Monthly Average of Employees YTD/Rolling12

Anonymous — Mon, 29 Nov 2021 23:20:23 GMT

Hello! I am working on a turnover report that will become a rolling 12 month report after January. I have lists of all of our employees for the first and last day of each month.

I need the table to show the monthly average (currently only carrying over the one month information). This is this information in the table.

Location --> Department --> Job Title -->Count and Calculations for Monthly Terms and FTE Reductions. All of the data for which employee is in the month is in one spreadsheet and is added to monthly. It is separated out by a column for Date which includes the first and last date for each month.

Anyone know how to get the average to calculate right?

I have tried separating them out and uploading them that way but it shows each month as a count in the table so January would show all the other months as well.

I have connected this information to a date table as well to attempt to make the count easier.

Re: Monthly Average of Employees YTD/Rolling12

VahidDM — Tue, 30 Nov 2021 01:24:23 GMT

Hi Anonymous

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Monthly Average of Employees YTD/Rolling12

v-robertq-msft — Thu, 02 Dec 2021 03:30:33 GMT

Hi,

According to your description, I can roughly understand your requirement, you want to get the Rolling 12 monthly average of the sales month of the employees in your company, right? I think you can try this measure:

This is the test data I created based on your description:

Rolling 12 monthly average = CALCULATE ( AVERAGE ( 'Table'[Sales] ), FILTER ( ALL ( 'Table' ), [Employee] = MAX ( 'Table'[Employee] ) && [Date] > EOMONTH ( MAX ( 'Table'[Date] ), -13 ) && [Date] <= EOMONTH ( MAX ( 'Table'[Date] ), 0 ) ) )

And you can create a table chart to place like this to get what you want.

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Monthly Average of Employees YTD/Rolling12

Anonymous — Fri, 31 Dec 2021 20:41:49 GMT

This is perfect- Thank you. Apologies for a late response. Illness had me on the sidelines.