Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have two tables that are connected through a calendar table. The problem I am having is that in the first table I need to calculate an average of the last 12 months only even if the dates go back further and then divide that average by the total number of people in the second table e.g.
First Table
Department
Department Staff number Start Date
1 12345 10/10/2015
2 56745 01/09/2019
3 10101 15/10/2018
4 20202 20/01/2016
Second Table
Staff
Department Staff number Leaving Date
1 12345 01/03/2017
2 56745 22/09/2019
3 10101 13/04/2020
4 20202 19/10/2019
The calculation I have for the first table to calculate the average is
@UK_User123456 - Please check this out, let me know if this works. If this fix your problem, please tick this a solution and a thumps up.
Staff 12 MONTHS =
CALCULATE(
COUNTROWS('DEPARTMENT'),
FILTER(
ALL('Calendar'[Date]),
'Calendar'[Date] > TODAY() - 365 && 'Calendar'[Date] <= TODAY()
),
RELATEDTABLE('DEPARTMENT')
)
Total Staff = COUNTROWS('STAFF')
Average =
DIVIDE(
[Staff 12 MONTHS],
[Total Staff],
0
)
Hi @Manoj_Nair ,
How would the calculation work if put into the calculation I currently have in my post, as I need to be able to show these split down by month, so showing the split out by month in a matrix table?
Thanks
Hi @Manoj_Nair ,
Many thanks for this, is this meant to be two seperate calculation measures or one?