HR - Inventory, Tenure, & Salary

Download Sample cloud_download

HR - Inventory, Tenure, & Salary

This sample takes the following inventory, and salary data:…

Click to view large-sized image

HR Inventory, Tenure, & Salary Metrics

…and shows the following HR Inventory, Tenure, & Salary metrics:

  1. External Employee Inventory Movements, by Month & Quarter
  2. External & Internal Employee Inventory Movements (changes in Country, Office, or Department only), by Month & Quarter
  3. Running Tenure (in Years), and Running Tenure by Job Grade (in Months)
  4. Monthly HeadCount by Gender, and Department - as absolute figures, and percentages
  5. Total Salary by Gender, and Department
  6. Average Salary per Person, by Gender and Department
  7. New Hires, and Leavers: Totalv, *Male only, and Female only
  8. For a chosen Month: Headcount by Gender, Job Grade, Employment Status
  9. For a chosen Month: A Histogram of Runnning Tenure in Yrs

Measures are also filterable by: Office, Department, Job Grade, and Month.

Model Extensions

Possible extensions to this model, could include:

  • Adding ‘Freelance or Permanent’ as an extra field to distinguish between amounts paid to these different staff types
  • Separating the calculations for Freelancers and Permanent Staff so that the Running Tenure, Running Salary and Weighted Average Salary calculations only consider the time when a person was a Freelancer, or only when they were Permanent Staff, or both.
  • Freelance records could be automatically linked and created from their agency Timesheet submissions.
  • Consideration of Public Holidays for the Salary calculations

Dashboard - Snippet

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Data Model - Snippet

Click to view large-sized image

Click to view large-sized image

Measures Headcount

Salary in Month GBP

DAX 
Count User ID = 
COUNTA ( 'Cross Joined Staff Table (DAX)'[User ID] )

Head Count at End of Month

DAX 
Head Count at End of Month = CALCULATE (
    COUNTA ( 'Cross Joined Staff Table (DAX)'[User ID] ),
    'Cross Joined Staff Table (DAX)'[Part of HeadCount at End of Month] = "Y",
    FILTER('Cross Joined Staff Table (DAX)', 'Cross Joined Staff Table (DAX)'[Start of Month] <= TODAY())
)

Head Count at End of Month PM

DAX 
Head Count at End of Month PM = 
CALCULATE([Head Count at End of Month], PREVIOUSMONTH('Cross Joined Staff Table (DAX)'[Start of Month]))

Head Count at End of Quarter

DAX 
Head Count at End of Quarter = 
CALCULATE (
    [Head Count at End of Month],
    FILTER (
        'Cross Joined Staff Table (DAX)',
        'Cross Joined Staff Table (DAX)'[Start of Month]
            = MAX ( 'Cross Joined Staff Table (DAX)'[Start of Month] )
    ),
    FILTER('Cross Joined Staff Table (DAX)', 'Cross Joined Staff Table (DAX)'[Start of Month] <= TODAY())
)

Head Count at Start of Month

DAX 
Head Count at Start of Month = CALCULATE (
    COUNTA ( 'Cross Joined Staff Table (DAX)'[User ID] ),
    'Cross Joined Staff Table (DAX)'[Part of HeadCount at Start of Month] = "Y",
    FILTER('Cross Joined Staff Table (DAX)', 'Cross Joined Staff Table (DAX)'[Start of Month] <= TODAY())
)

Head Count at Start of Quarter

DAX 
Head Count at Start of Quarter = 
CALCULATE (
    [Head Count at Start of Month],
    FILTER (
        'Cross Joined Staff Table (DAX)',
        'Cross Joined Staff Table (DAX)'[Start of Month]
            = MIN ( 'Cross Joined Staff Table (DAX)'[Start of Month] )
    ),
    FILTER('Cross Joined Staff Table (DAX)', 'Cross Joined Staff Table (DAX)'[Start of Month] <= TODAY())
)

Leavers (External) as % Starting HeadCount

DAX 
Leavers (External) as % Starting HeadCount = 
DIVIDE([Leavers in Month (External)], [Head Count at Start of Month], BLANK())

Leavers in Month (Both)

DAX 
Leavers in Month (Both) = 
CALCULATE (
    COUNTA ( 'Cross Joined Staff Table (DAX)'[User ID]),
    'Cross Joined Staff Table (DAX)'[Left in Month ?] = "Y",
    FILTER('Cross Joined Staff Table (DAX)','Cross Joined Staff Table (DAX)'[Internal Leave - C-O-D] = "Y" || 'Cross Joined Staff Table (DAX)'[External Leave] = "Y"),
    FILTER('Cross Joined Staff Table (DAX)', 'Cross Joined Staff Table (DAX)'[Start of Month] <= TODAY())
)*-1

Leavers in Month (External)

DAX 
Leavers in Month (External) = 
VAR Leavers =
CALCULATE (
    IF(COUNTA ( 'Cross Joined Staff Table (DAX)'[User ID]) = BLANK(), 0, COUNTA ( 'Cross Joined Staff Table (DAX)'[User ID])),
    'Cross Joined Staff Table (DAX)'[Left in Month ?] = "Y",
    'Cross Joined Staff Table (DAX)'[External Leave] = "Y",
    FILTER('Cross Joined Staff Table (DAX)', 'Cross Joined Staff Table (DAX)'[Start of Month] <= TODAY())
)*-1
RETURN
IF(MAX('Cross Joined Staff Table (DAX)'[Start of Month]) > TODAY(), BLANK(), Leavers)

Leavers in Month (External) PM

DAX 
Leavers in Month (External) PM = 
CALCULATE([Leavers in Month (External)], PREVIOUSMONTH('Cross Joined Staff Table (DAX)'[Start of Month]))

New Hires (External) as % Starting HeadCount

DAX 
New Hires (External) as % Starting HeadCount = 
DIVIDE([New Hires in Month (External)], [Head Count at Start of Month], BLANK())

New Hires in Month (Both)

DAX 
New Hires in Month (Both) = 
CALCULATE (
    COUNTA ( 'Cross Joined Staff Table (DAX)'[User ID]),
    'Cross Joined Staff Table (DAX)'[Started in Month ?] = "Y",
    FILTER('Cross Joined Staff Table (DAX)','Cross Joined Staff Table (DAX)'[Internal Join - C-O-D] = "Y" || 'Cross Joined Staff Table (DAX)'[External Join] = "Y"),
    FILTER('Cross Joined Staff Table (DAX)', 'Cross Joined Staff Table (DAX)'[Start of Month] <= TODAY())
)

New Hires in Month (External)

DAX 
New Hires in Month (External) = 
VAR NewHires =
CALCULATE (
    IF(COUNTA ('Cross Joined Staff Table (DAX)'[User ID]) = BLANK(), 0, COUNTA ('Cross Joined Staff Table (DAX)'[User ID])),
    'Cross Joined Staff Table (DAX)'[Started in Month ?] = "Y",
    'Cross Joined Staff Table (DAX)'[External Join] = "Y",
    FILTER('Cross Joined Staff Table (DAX)', 'Cross Joined Staff Table (DAX)'[Start of Month] <= TODAY())
)
RETURN
IF(MAX('Cross Joined Staff Table (DAX)'[Start of Month]) > TODAY(), BLANK(), NewHires)

New Hires in Month (External) PM

DAX 
New Hires in Month (External) PM = 
CALCULATE([New Hires in Month (External)], PREVIOUSMONTH('Cross Joined Staff Table (DAX)'[Start of Month]))

Today

DAX 
Today = TODAY()

Measures Salary

Average Salary by Person (Actual) GBP

DAX 
Average Salary by Person (Actual) GBP = 
AVERAGEX (
    SUMMARIZE (
        'Cross Joined Staff Table (DAX)',
        'Cross Joined Staff Table (DAX)'[User ID],
        'Cross Joined Staff Table (DAX)'[Start of Month]
    ),
    CALCULATE ( SUM ( 'Cross Joined Staff Table (DAX)'[Salary in Month GBP] ) )
)

Average Salary by Person (Actual) GBP PM

DAX 
Average Salary by Person (Actual) GBP PM = 
CALCULATE([Average Salary by Person (Actual) GBP], PREVIOUSMONTH('Cross Joined Staff Table (DAX)'[Start of Month]))

Total Salary (Actual) GBP

DAX 
Total Salary (Actual) GBP = 
VAR MonthlySalaryGBP =
    CALCULATE ( SUM ( 'Cross Joined Staff Table (DAX)'[Salary in Month GBP] ) )
RETURN
    IF ( MonthlySalaryGBP = 0, BLANK (), MonthlySalaryGBP )

Total Salary (Actual) GBP PM

DAX 
Total Salary (Actual) GBP PM = 
CALCULATE([Total Salary (Actual) GBP], PREVIOUSMONTH('Cross Joined Staff Table (DAX)'[Start of Month]))

Measures Tenure

Job Grade Tenure Mnths Measure

DAX 
Job Grade Tenure Mnths Measure = 
VAR JobGradeTenureMths =
    CALCULATE (
        AVERAGE ( 'Cross Joined Staff Table (DAX)'[Running Tenure Mths - by Job Grade] ),
        FILTER (
            'Cross Joined Staff Table (DAX)',
            'Cross Joined Staff Table (DAX)'[Start Date]
                = MAX ( 'Cross Joined Staff Table (DAX)'[Start Date] )
        )
    )
RETURN
    IF (
        AND (
            ISFILTERED ( 'Cross Joined Staff Table (DAX)'[Job Grade] ),
            ISFILTERED ( 'Cross Joined Staff Table (DAX)'[User ID] )
        ),
        JobGradeTenureMths,
        SUMX (
            SUMMARIZE (
                'Cross Joined Staff Table (DAX)',
                [User ID],
                [Job Grade],
                [Start of Month],
                "Job Grade Tenure Month",
                    CALCULATE (
                        AVERAGE ( 'Cross Joined Staff Table (DAX)'[Running Tenure Mths - by Job Grade] ),
                        FILTER (
                            'Cross Joined Staff Table (DAX)',
                            'Cross Joined Staff Table (DAX)'[Start Date]
                                = MAX ( 'Cross Joined Staff Table (DAX)'[Start Date] )
                        )
                    )
            ),
            [Job Grade Tenure Month]
        )
    )

Job Grade Tenure Mnths Measure (Alternative)

DAX 
Job Grade Tenure Mnths Measure (Alternative) = 
VAR JobGradeTenureMths =
    CALCULATE (
        SUM ( 'Cross Joined Staff Table (DAX)'[Days in Service (incl. W/E)] ),
        ALLEXCEPT (
            'Cross Joined Staff Table (DAX)',
            'Cross Joined Staff Table (DAX)'[User ID],
            'Cross Joined Staff Table (DAX)'[Job Grade],
            'Cross Joined Staff Table (DAX)'[Start of Month]
        )
    ) * 12 / 365.25
RETURN
    IF (
        AND (
            ISFILTERED ( 'Cross Joined Staff Table (DAX)'[Job Grade] ),
            ISFILTERED ( 'Cross Joined Staff Table (DAX)'[User ID] )
        ),
        JobGradeTenureMths,
        SUMX (
            SUMMARIZE (
                'Cross Joined Staff Table (DAX)',
                [User ID],
                [Job Grade],
                [Start of Month],
                "Job Grade Tenure Month",
                    CALCULATE (
                        SUM ( 'Cross Joined Staff Table (DAX)'[Days in Service (incl. W/E)] ),
                        ALLEXCEPT (
                            'Cross Joined Staff Table (DAX)',
                            'Cross Joined Staff Table (DAX)'[User ID],
                            'Cross Joined Staff Table (DAX)'[Job Grade],
                            'Cross Joined Staff Table (DAX)'[Start of Month]
                        )
                    ) * 12 / 365.25
            ),
            [Job Grade Tenure Month]
        )
    )

Running Tenure Years Measure

DAX 
Running Tenure Years Measure = 
VAR TenureYrs =
    CALCULATE (
        AVERAGE ( 'Cross Joined Staff Table (DAX)'[Running Tenure Yrs] ),
        FILTER (
            'Cross Joined Staff Table (DAX)',
            'Cross Joined Staff Table (DAX)'[Start Date]
                = MAX ( 'Cross Joined Staff Table (DAX)'[Start Date] )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Cross Joined Staff Table (DAX)'[User ID] ),
        TenureYrs,
        AVERAGEX (
            SUMMARIZE (
                'Cross Joined Staff Table (DAX)',
                [User ID],
                [Start of Month],
                "Tenure Years",
                    CALCULATE (
                        AVERAGE ( 'Cross Joined Staff Table (DAX)'[Running Tenure Yrs] ),
                        FILTER (
                            'Cross Joined Staff Table (DAX)',
                            'Cross Joined Staff Table (DAX)'[Start Date]
                                = MAX ( 'Cross Joined Staff Table (DAX)'[Start Date] )
                        )
                    )
            ),
            [Tenure Years]
        )
    )

Running Tenure Years Measure (Alternative)

DAX 
Running Tenure Years Measure (Alternative) = 
VAR TenureYrs =
    CALCULATE (
        SUM ( 'Cross Joined Staff Table (DAX)'[Days in Service (incl. W/E)] ),
        ALLEXCEPT (
            'Cross Joined Staff Table (DAX)',
            'Cross Joined Staff Table (DAX)'[User ID],
            'Cross Joined Staff Table (DAX)'[Start of Month]
        )
    ) / 365.25
RETURN
    IF (
        ISFILTERED ( 'Cross Joined Staff Table (DAX)'[User ID] ),
        TenureYrs,
        AVERAGEX (
            SUMMARIZE (
                'Cross Joined Staff Table (DAX)',
                [User ID],
                [Start of Month],
                "Tenure Years",
                    CALCULATE (
                        SUM ( 'Cross Joined Staff Table (DAX)'[Days in Service (incl. W/E)] ),
                        ALLEXCEPT (
                            'Cross Joined Staff Table (DAX)',
                            'Cross Joined Staff Table (DAX)'[User ID],
                            'Cross Joined Staff Table (DAX)'[Start of Month]
                        )
                    ) / 365.25
            ),
            [Tenure Years]
        )
    )

Running Tenure Years PM

DAX 
Running Tenure Years PM = 
CALCULATE([Running Tenure Years Measure], PREVIOUSMONTH('Cross Joined Staff Table (DAX)'[Start of Month]))

Tables

Calendar

DAX 
Calendar = 
VAR MinDate =
    MIN ( Min_Date[Min_Date] )
VAR MaxDate =
    MAX ( Max_Date[Max_Date] )
RETURN
    CALENDAR ( EOMONTH ( MinDate, - 1 ) + 1, EOMONTH ( MaxDate, 0 ) )

Cross Joined Staff Table (DAX)

DAX 
Cross Joined Staff Table (DAX) = 
CROSSJOIN('Monthly Calendar', Inventory_Table)

Calculated Columns

Weekend

DAX 
Weekend = 
SWITCH ( WEEKDAY ( [Date] ), 1, 1, 7, 1, 0 )

Annual Salary GBP

DAX 
Annual Salary GBP = 
'Cross Joined Staff Table (DAX)'[Ratio] * 'Cross Joined Staff Table (DAX)'[FTE Salary GBP]

Days in Service (excl W/E)

DAX 
Days in Service (excl W/E) = 
VAR StartDate = [Start Date]
VAR EndDate =
    IF ( ISBLANK ( [End Date] ), TODAY (), [End Date] )
RETURN
    IF (
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] ) < StartDate,
        BLANK (),
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] ) - StartDate + 1 - [Weekend Days in Service]
    )

Days in Service (incl. W/E)

DAX 
Days in Service (incl. W/E) = 
VAR StartDate = [Start Date]
VAR EndDate =
    IF ( ISBLANK ( [End Date] ), TODAY (), [End Date] )
RETURN
    IF (
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] ) < StartDate,
        BLANK (),
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] ) - StartDate + 1
    )

Days in Service in Month (excl W/E)

DAX 
Days in Service in Month (excl W/E) = 
VAR StartDate =
    MAX ( [Start Date], [Start of Month] )
VAR EndDate =
    IF ( ISBLANK ( [End Date] ), TODAY (), [End Date] )
RETURN
    IF (
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] ) < StartDate,
        BLANK (),
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] ) - StartDate + 1 - [Weekend Days in Service in Month]
    )

End Date Fill

DAX 
End Date Fill = 
IF ( ISBLANK ( [End Date] ), 2958100, [End Date] )

External Join

DAX 
External Join = 
IF(([Start Date] - [Previous End Date]) > 30, "Y", "N")

External Leaves

DAX 
External Leave = 
IF(([Next Start Date] - [End Date Fill]) > 30, "Y", "N")

Internal Join - C-O-D

DAX 
Internal Join - C-O-D = 
VAR PreviousCountry =
    CALCULATE (
        MAX ( 'Cross Joined Staff Table (DAX)'[Country] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            'Cross Joined Staff Table (DAX)'[End Date]
                = EARLIER ( 'Cross Joined Staff Table (DAX)'[Previous End Date] )
        )
    )
VAR PreviousOffice =
    CALCULATE (
        MAX ( 'Cross Joined Staff Table (DAX)'[Office] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            'Cross Joined Staff Table (DAX)'[End Date]
                = EARLIER ( 'Cross Joined Staff Table (DAX)'[Previous End Date] )
        )
    )
VAR PreviousDepartment =
    CALCULATE (
        MAX ( 'Cross Joined Staff Table (DAX)'[Department] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            'Cross Joined Staff Table (DAX)'[End Date]
                = EARLIER ( 'Cross Joined Staff Table (DAX)'[Previous End Date] )
        )
    )
RETURN
    IF (
        AND (
            [External Join] = "N",
            [Country] <> PreviousCountry
                || [Office] <> PreviousOffice
                || [Department] <> PreviousDepartment
        ),
        "Y",
        "N"
    )

Internal Leave - C-O-D

DAX 
Internal Leave - C-O-D = 
VAR NextCountry =
    CALCULATE (
        MIN ( 'Cross Joined Staff Table (DAX)'[Country] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            'Cross Joined Staff Table (DAX)'[Start Date]
                = EARLIER ( 'Cross Joined Staff Table (DAX)'[Next Start Date] )
        )
    )
VAR NextOffice =
    CALCULATE (
        MIN ( 'Cross Joined Staff Table (DAX)'[Office] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            'Cross Joined Staff Table (DAX)'[Start Date]
                = EARLIER ( 'Cross Joined Staff Table (DAX)'[Next Start Date] )
        )
    )
VAR NextDepartment =
    CALCULATE (
        MIN ( 'Cross Joined Staff Table (DAX)'[Department] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            'Cross Joined Staff Table (DAX)'[Start Date]
                = EARLIER ( 'Cross Joined Staff Table (DAX)'[Next Start Date] )
        )
    )
RETURN
    IF (
        AND (
            [External Leave] = "N",
            [Country] <> NextCountry
                || [Office] <> NextOffice
                || [Department] <> NextDepartment
        ),
        "Y",
        "N"
    )

Left in Month ?

DAX 
Left in Month ? = 
IF (
    AND ( [End Date Fill] >= [Start of Month], [End Date Fill] <= [End of Month] ),
    "Y",
    "N"
)

Next Start Date

DAX 
Next Start Date = 
IF (
    ISBLANK (
        CALCULATE (
            MIN ( 'Cross Joined Staff Table (DAX)'[Start Date] ),
            FILTER (
                ALLEXCEPT (
                    'Cross Joined Staff Table (DAX)',
                    'Cross Joined Staff Table (DAX)'[User ID],
                    'Cross Joined Staff Table (DAX)'[Start of Month]
                ),
                'Cross Joined Staff Table (DAX)'[Start Date]
                    > EARLIER ( 'Cross Joined Staff Table (DAX)'[Start Date] )
            )
        )
    ),
    2958465,
    CALCULATE (
        MIN ( 'Cross Joined Staff Table (DAX)'[Start Date] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            'Cross Joined Staff Table (DAX)'[Start Date]
                > EARLIER ( 'Cross Joined Staff Table (DAX)'[Start Date] )
        )
    )
)

Part of HeadCount at End of Month

DAX 
Part of HeadCount at End of Month = 
IF (
    AND ( [Start Date] <= [End of Month], [End Date Fill] > [End of Month] ),
    "Y",
    "N"
)

Part of HeadCount at Start of Month

DAX 
Part of HeadCount at Start of Month = 
IF (
    AND ( [Start Date] < [Start of Month], [End Date Fill] >= [Start of Month] ),
    "Y",
    "N"
)

Part of HeadCount Start or End of Month

DAX 
Part of HeadCount Start or End of Month = 
IF (
    OR (
        [Part of HeadCount at Start of Month] = "Y",
        [Part of HeadCount at End of Month] = "Y"
    ),
    "Y",
    "N"
)

Previous End Date

DAX 
Previous End Date = 
IF (
    ISBLANK (
        CALCULATE (
            MAX ( [End Date Fill] ),
            FILTER (
                ALLEXCEPT (
                    'Cross Joined Staff Table (DAX)',
                    'Cross Joined Staff Table (DAX)'[User ID],
                    'Cross Joined Staff Table (DAX)'[Start of Month]
                ),
                [End Date Fill] < EARLIER ( 'Cross Joined Staff Table (DAX)'[End Date Fill] )
            )
        )
    ),
    0,
    CALCULATE (
        MAX ( [End Date Fill] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            [End Date Fill] < EARLIER ( 'Cross Joined Staff Table (DAX)'[End Date Fill] )
        )
    )
)

Running Tenure Mths - by Job Grade

DAX 
Running Tenure Mths - by Job Grade = 
CALCULATE (
    SUM ( [Days in Service (incl. W/E)] ),
    ALLEXCEPT (
        'Cross Joined Staff Table (DAX)',
        'Cross Joined Staff Table (DAX)'[Start of Month],
        'Cross Joined Staff Table (DAX)'[User ID],
        'Cross Joined Staff Table (DAX)'[Job Grade]
    )
) / 365.25 * 12

Running Tenure Yrs

DAX 
Running Tenure Yrs = 
CALCULATE (
    SUM ( [Days in Service (incl. W/E)] ),
    ALLEXCEPT (
        'Cross Joined Staff Table (DAX)',
        'Cross Joined Staff Table (DAX)'[Start of Month],
        'Cross Joined Staff Table (DAX)'[User ID]
    )
) / 365.25

Salary in Month GBP

DAX 
Salary in Month GBP = 
VAR DaysinMonth = [End of Month] - [Start of Month] + 1
VAR WeekendDaysinMonth =
    CALCULATE (
        SUM ( 'Calendar'[Weekend] ),
        DATESBETWEEN (
            'Calendar'[Date],
            'Cross Joined Staff Table (DAX)'[Start of Month],
            'Cross Joined Staff Table (DAX)'[End of Month]
        )
    )
RETURN
    [Annual Salary GBP] * ( 1 / 12 )
        * DIVIDE (
            [Days in Service in Month (excl W/E)],
             ( DaysinMonth - WeekendDaysinMonth ),
            0
        )

Started in Month ?

DAX 
Started in Month ? = 
IF (
    AND ( [Start Date] >= [Start of Month], [Start Date] <= [End of Month] ),
    "Y",
    "N"
)

Tenure Bracket

DAX 
Tenure Bracket = 
SWITCH (
    TRUE (),
    [Running Tenure Yrs] = 0, "Nil",
    [Running Tenure Yrs] > 0
        && [Running Tenure Yrs] <= 0.5, "0 to 0.5 years",
    [Running Tenure Yrs] > 0.5
        && [Running Tenure Yrs] <= 1, "0.5 to 1 year",
    [Running Tenure Yrs] > 1
        && [Running Tenure Yrs] <= 3, "1 to 3 years",
    [Running Tenure Yrs] > 3
        && [Running Tenure Yrs] <= 5, "3 to 5 years",
    [Running Tenure Yrs] > 5
        && [Running Tenure Yrs] <= 8, "5 to 8 years",
   [Running Tenure Yrs] > 8
        && [Running Tenure Yrs] <= 1000, "More than 8 years",
    "Other"
)

Weekend Days in Service

DAX 
Weekend Days in Service = 
VAR StartDate = [Start Date]
VAR EndDate =
    IF ( ISBLANK ( [End Date] ), TODAY (), [End Date] )
RETURN
    IF (
        MIN (
            MIN ( TODAY (), EndDate ),
            'Cross Joined Staff Table (DAX)'[End of Month]
        ) < StartDate,
        BLANK (),
        CALCULATE (
            SUM ( 'Calendar'[Weekend] ),
            DATESBETWEEN (
                'Calendar'[Date],
                StartDate,
                MIN (
                    MIN ( TODAY (), EndDate ),
                    'Cross Joined Staff Table (DAX)'[End of Month]
                )
            )
        )
    )

Weekend Days in Service

DAX 
Weekend Days in Service in Month = 
VAR StartDate =  MAX ( [Start Date], [Start of Month] )
VAR EndDate =
    IF ( ISBLANK ( [End Date] ), TODAY (), [End Date] )
RETURN
    IF (
        MIN (
            MIN ( TODAY (), EndDate ),
            'Cross Joined Staff Table (DAX)'[End of Month]
        ) < StartDate,
        BLANK(),
        CALCULATE (
            SUM ( 'Calendar'[Weekend] ),
            DATESBETWEEN (
                'Calendar'[Date],
                StartDate,
                MIN (
                    MIN ( TODAY (), EndDate ),
                    'Cross Joined Staff Table (DAX)'[End of Month]
                )
            )
        )
    )