Averages

[UK_User123456]

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]

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

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

[UK_User123456]

Hi @Manoj_Nair ,

Many thanks for this, is this meant to be two seperate calculation measures or one?