Averages

UK_User123456 · ‎08-10-2023

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

Dep =

var MinDate = MIN('Calendar'[Date])

var MaxDate = MAX('Calendar'[Date])

var DepTotal =

CALCULATE( DIVIDE(COUNT(Department[Staff Number]),DISTINCTCOUNT(Department[Staff Number])),

Department[Start Date]>= MinDate

&& Department[Start Date] <= MaxDate,

NOT(ISBLANK(Headcount[Start Date]))

)

Return

DepTotal

The calculation I have for the second table is:

CStaff =

var MinDate = MIN('Calendar'[Date])

var MaxDate = MAX('Calendar'[Date])

var StaffTotal =

CALCULATE( COUNT(Staff[Staff Number]),

Staff[Leaving Date]>= MinDate

&& Staff[Leaving Date] <= MaxDate,

NOT(ISBLANK(Staff[Leaving Date]))

)

Return

StaffTotal

Any help would would be great.

TIA

Manoj_Nair · ‎08-10-2023

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

UK_User123456 · ‎08-10-2023

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