Doing a count based on the values in multiple tables

[MCMcMax]

Hello everyone - hope you're well.

I have one table which is a list of all our staff, both past and present, each with a unique ID.

Using the unique ID, this table has a 1 to many relationship with 2 other tables:

• A list of departments they have been a member of with the date they started in the department
• A list of the number of hours they do a week with the date that any change to their hours was made - effectively these hours divided by the standard week of 37.5 hours gives how many FTEs they are.

I also have a separate date table.

So a staff member may both change departments while they are working for us and change their weekly working hours.

I'd like to create a measure to calculate over a given period:

• Average FTEs for a department over a chosen period
• values to enable a line graph to show the average FTEs profile over a chosen period

I'm guessing it's either going to:

• Some sort of joint filtering
• Some way of joining up the two changes tables into one?

[Anonymous]

Hi  @MCMcMax ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

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