HR - Showing Employee Salaries & analysing trends in Actual and Average Running (Pro-Rated) Salaries over time

Guides • Data Analytics • 17/09/2020

HR - Showing Employee Salaries & analysing trends in Actual and Average Running (Pro-Rated) Salaries over time

This sample takes the following data (you need to have a HR ERP supports such data capture!), and turns it into a full Employee Inventory Salary Table. In this example, the final data table (after any necessary cross-joining) is created entirely with DAX (here, ETL, and ‘M’ code are not used).

Click to view large-sized image

The example in this article is slightly more complex than the Tenure example shown in this DAX article: HR - Showing Inventory Movements & analysing Total Running Tenure over time, to calculate Tenure. That example shows: (a) each Employee’s Running Tenure (in Years) by Month - showing only one figure for a user, in a given Month. It also shows (b) each Employee’s Running Tenure (in Months) by Job Grade, and Month - again, showing only one figure for a user, for a given Month and Job Grade.

The way Running Tenure is calculated, to all intents and purposes good enough, and we can get by, without having to be Hyper-Accurate!

In this example, however, we take things a step further. Probably the most difficult measure shown in this report is:

(i) Running Average Monthly Salary by Grade: (shown on page 2 of the report).

The measure is re-computed everytime a user’s profile changes - even if looking at the same month, where the user’s job grade does not change - for example, if there are other profile changes, such as in their: Department, Country, Full or Part Time working status, over the course of the month.

Running Average Monthly Salary Methodology

The Running Average Monthly Salary calculation is complex (but also as accurate as I could make it!). For each Employee, the measure is computed, by Job Grade, by:

Taking the Arithmetic average of: (i) the Weighted Average salary of the Employee in the current month (upto the end date of current profile change), together with (ii) the sum of the Weighted Average salary of the Employee in each of the previous months, as they stood at the end of those months (but still considering only those months where the Employee held the same Job Grade - as the measure is computed at a Job Grade level),
The Weighted Average Monthly Salary, upto a particular date, itself, is also re-computed for each profile change, and calculated by taking a blended average of the Employee’s Pro-Rated (Full Month) Salaries, for each unique profile position held by the Employee in the given month (i.e. from the start of the month (or first start date at that particular job grade, which ever is later) upto the given end date of the profile the Weighted Average Salary is being calculated for).

Worked Example

To illustrate the method more clearly:

If, in January, Employee 001, worked 5 days, as an Associate, in Brazil, from 27/01/2020 to 31/01/2020, on a Pro-Rated Monthly Salary of £5,000, the Weighted Average Salary on 31/01/2020 is: £5,000/5 * 5 = £5,000. Their Running Average Monthly Salary would be £5,000 / 1 = £5,000 (Note here, if the employee just started on 27/01/2020, their actual paid salary would be: £5,000 * 5 / 31 = £806, in the Month)

If Employee 001 then worked for another 10 days, from 01/02/2020 to 10/02/2020, on a Pro-Rated Monthly Salary of £6,000, their Weighted Average Salary on 10/02/2020 would be: £6,000/10 * 10 = £6,000. Their Running Average Salary, upto this date, would be: ( £5,000 (from Jan) + £6,000 (from Feb) ) / 2 = £5,500

If then, the Employee then worked for 15 days, from 11/02/2020 to 25/02/2020, on a Pro-Rated Monthly Salary of £5,000, their Weighted Average Salary on 25/02/2020 would be: (£6,000 * 10 + £5,000 * 15) / 25 = £5,400. Their Running Average Monthly Salary would change to: ( £5,000 (from Jan) + £5,400 (from Feb) ) / 2 = £5,200

Other Measures

Apart from Running Average Monthly Salary by Grade, the report also shows:

(iii) The Actual Salary Paid to each Employee:, by Job Grade, and Month, both as a Matrix Table, and Stacked Bar Chart (on page 1)
(ii) The Actual Salary Paid to each Employee:, by Country, Department, Part of Full time working status, and Job Grade (on page 2)

Enhancements

Other enhancements to make the calculation more accurate, have been to:

Ignore the affect of Weekends in any day count calculations (when computing any Weighted Average, Actual Paid, or Pro-Rated Salaries)
Reduce the Weighted Average Salary calculation, for any Working days not worked in the month
- For example, assuming weekends don’t exist, if an Employee worked from 01/02/2020 to 10/02/2020, and then from 15/02/2020 to 20/02/2020, the Days in Service worked by the Employee would be 16. The Days Available to that Employee in the Month would be 20 (because of the 4 days not worked). The affect would be to reduce their Weighted Average Salary when it is calculated on 20/02/2022. There would be no affect when the salary is calculated on 10/02/2020, because at this point, both Days in Service, and Days Available would have been 10)
- Note that, if the Employee started working slightly later, say on 03/02/2020 (i.e. not the first day of the month), to 10/02/2020, and then again from 15/02/2020 to 20/02/2020, their total Days in Service on 20/02/2020 would be 14, and Days Available would be 18. Again, provided 03/02/2020 was Employee’s very first day working at the particular Job Grade, the Weighted Average Salary when calculated at 10/02/2020 would not be affected. It would still though be affected when calculated on 20/02/2020.

Warning:
Public Holidays are not considered in this report. Only Weekends, which are assumed to occur every Saturday, and Sunday.

Note:
By comparison, the Running Tenure example, shown in this DAX article: HR - Showing Inventory Movements & analysing Total Running Tenure over time, takes a much simpler approach. Each Employee’s tenure (Tenure by employee, or Tenure by employee by job grade), is calculated by providing one running total figure - even if different profile statuses are held by the employee in the same month, at the same job grade. The calculation is done by scanning the entire month first to compute the running total figure for the entire month, rather than, what is being done above, where the running total is re-calculated for each profile change, within the course of the month.)

Model Extensions

Possible extensions to this model, could include:

Adding ‘Freelance or Permanent’ as an extra field to distinguish between these staff types
Separating the calculations for Freelancers and Permanent Staff so that the so that the 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

Dashboard - Snippet

Click to view large-sized image

Click to view large-sized image

Important:
In this report, in order to show the correct results, most tables and charts have additional Page filters applied to them too. Click the red filter bar on the side of each page, to see which filters have been applied to each table and chart.

Data Model - Snippet

Click to view large-sized image

Click to view large-sized image

Measures

Salary in Month GBP

DAX

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

Running Avg. Monthly Salary GBP (Pro Rated) - by Job Grade

DAX

Running Avg. Monthly Salary GBP (Pro Rated) - by Job Grade = 
VAR RunningAverageMonthlySalaryJobGrade =
    CALCULATE (
        AVERAGE ( 'Cross Joined Staff Table (DAX)'[Running Average Salary in Month GBP by Grade] ),
        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] ),
        RunningAverageMonthlySalaryJobGrade,
        AVERAGEX (
            SUMMARIZE (
                '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],
                "Running Average Monthly Salary by Job Grade",
                    CALCULATE (
                        AVERAGE ( 'Cross Joined Staff Table (DAX)'[Running Average Salary in Month GBP by Grade] ),
                        FILTER (
                            'Cross Joined Staff Table (DAX)',
                            'Cross Joined Staff Table (DAX)'[Start Date]
                                = MAX ( 'Cross Joined Staff Table (DAX)'[Start Date] )
                        )
                    )
            ),
            [Running Average Monthly Salary by Job Grade]
        )
    )

Today

DAX

Today = TODAY()

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]

Count of Months with Salary by Grade

DAX

Count of Months with Salary by Grade = 
VAR CountofMonthswithSalary =
    CALCULATE (
        DISTINCTCOUNT ( 'Cross Joined Staff Table (DAX)'[Start of Month] ),
        FILTER (
            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]
                <= EARLIER ( 'Cross Joined Staff Table (DAX)'[Start of Month] )
                && 'Cross Joined Staff Table (DAX)'[Days in Service in Month (excl W/E)] <> 0
                && 'Cross Joined Staff Table (DAX)'[Start Date]
                    <= EARLIER ( 'Cross Joined Staff Table (DAX)'[Start Date] )
        )
    )
RETURN
IF('Cross Joined Staff Table (DAX)'[Days in Service (excl W/E)] = 0, BLANK(), CountofMonthswithSalary)

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 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 Leave

DAX

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

Internal Join

DAX

Internal Join = 
IF([External Join] = "Y", "N", "Y")

Internal Leave

DAX

Internal Leave = 
IF([External Leave] = "Y", "N", "Y")

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 Average Salary in Month GBP by Grade

DAX

Running Average Salary in Month GBP by Grade = 
DIVIDE (
    'Cross Joined Staff Table (DAX)'[Running Total Salary in Month GBP (Pro Rated) by Grade],
    'Cross Joined Staff Table (DAX)'[Count of Months with Salary by Grade],
    0
)

Running Total Salary in Month GBP (Pro Rated) by Grade

DAX

Running Total Salary in Month GBP (Pro Rated) by Grade = 
VAR FilteredTable =
    CALCULATETABLE (
        'Cross Joined Staff Table (DAX)',
        FILTER (
            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]
                < EARLIER ( 'Cross Joined Staff Table (DAX)'[Start of Month] )
                && 'Cross Joined Staff Table (DAX)'[Start Date]
                    <= EARLIER ( 'Cross Joined Staff Table (DAX)'[Start Date] )
                && 'Cross Joined Staff Table (DAX)'[Days in Service in Month (excl W/E)] <> 0
        )
    )
VAR RunningTotalSalaryinMonth =
    SUMX (
        SUMMARIZE (
            FilteredTable,
            'Cross Joined Staff Table (DAX)'[User ID],
            'Cross Joined Staff Table (DAX)'[Job Grade],
            'Cross Joined Staff Table (DAX)'[Start of Month],
            "ValueMaxDate",
                CALCULATE (
                    AVERAGE ( 'Cross Joined Staff Table (DAX)'[Total Weighted Salary in Month GBP (Pro Rated)] ),
                    FILTER (
                        'Cross Joined Staff Table (DAX)',
                        'Cross Joined Staff Table (DAX)'[Start Date]
                            = MAX ( 'Cross Joined Staff Table (DAX)'[Start Date] )
                    )
                )
        ),
        [ValueMaxDate]
    )
RETURN
    IF (
        'Cross Joined Staff Table (DAX)'[Days in Service (excl W/E)] = 0,
        BLANK (),
        RunningTotalSalaryinMonth + 'Cross Joined Staff Table (DAX)'[Total Weighted Salary in Month GBP (Pro Rated)]
    )

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
        )

Salary in Month GBP (Pro Rated)

DAX

Salary in Month GBP (Pro Rated) = 
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]
        )
    )
VAR SalaryinMonth =
    'Cross Joined Staff Table (DAX)'[Salary in Month GBP]
        * DIVIDE (
             ( DaysinMonth - WeekendDaysinMonth ),
            'Cross Joined Staff Table (DAX)'[Days in Service in Month (excl W/E)],
            0
        )
RETURN
    IF (
        'Cross Joined Staff Table (DAX)'[Days in Service in Month (excl W/E)] = 0,
        BLANK (),
        SalaryinMonth
    )

Started in Month ?

DAX

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

Total Weighted Salary in Month GBP (Pro Rated)

DAX

Total Weighted Salary in Month GBP (Pro Rated) = 
VAR MaxStartDatePreviousGrade =
    CALCULATE (
        MAX ( '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] )
                && 'Cross Joined Staff Table (DAX)'[Job Grade]
                    <> EARLIER ( 'Cross Joined Staff Table (DAX)'[Job Grade] )
        )
    )
VAR StartDateBeforePreviousGradeChange =
    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] > MaxStartDatePreviousGrade
        )
    )
VAR NextStartDateSameGrade =
    CALCULATE (
        MIN ( [Start Date] ),
        FILTER (
            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]
            ),
            [Start Date] > MIN ( [Start Date] )
        )
    )
VAR MinStartDateNextGrade =
    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] )
                && 'Cross Joined Staff Table (DAX)'[Job Grade]
                    <> EARLIER ( 'Cross Joined Staff Table (DAX)'[Job Grade] )
        )
    )
VAR EndDateBeforeNextGradeChange =
    CALCULATE (
        MAX ( 'Cross Joined Staff Table (DAX)'[End 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] < MinStartDateNextGrade
        )
    )
VAR EndDate =
    IF (
        NextStartDateSameGrade > [End of Month],
        EndDateBeforeNextGradeChange,
        [End Date]
    )
VAR RunningDaysinMonthfromParticularGrade =
    IF (
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] )
            < MAX ( StartDateBeforePreviousGradeChange, [Start of Month] ) + 1,
        BLANK (),
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] )
            - MAX ( StartDateBeforePreviousGradeChange, [Start of Month] ) + 1
    )
VAR RunningWeekendDaysinMonthfromParticularGrade =
    IF (
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] )
            < MAX ( StartDateBeforePreviousGradeChange, [Start of Month] ) + 1,
        BLANK (),
        CALCULATE (
            SUM ( 'Calendar'[Weekend] ),
            DATESBETWEEN (
                'Calendar'[Date],
                MAX (
                    StartDateBeforePreviousGradeChange,
                    'Cross Joined Staff Table (DAX)'[Start of Month]
                ),
                MIN (
                    MIN ( TODAY (), EndDate ),
                    'Cross Joined Staff Table (DAX)'[End of Month]
                )
            )
        )
    )
VAR TotalWeightedSalary =
    CALCULATE (
        SUMX (
            'Cross Joined Staff Table (DAX)',
            [Salary in Month GBP (Pro Rated)] * [Days in Service in Month (excl W/E)]
        ),
        FILTER (
            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]
            ),
            'Cross Joined Staff Table (DAX)'[Start Date]
                <= EARLIER ( 'Cross Joined Staff Table (DAX)'[Start Date] )
        )
    )
RETURN
    IF (
        'Cross Joined Staff Table (DAX)'[Days in Service in Month (excl W/E)] = 0,
        BLANK (),
        DIVIDE (
            TotalWeightedSalary,
            RunningDaysinMonthfromParticularGrade - RunningWeekendDaysinMonthfromParticularGrade,
            0
        )
    )

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 in Month

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

HR - Showing Employee Salaries & analysing trends in Actual and Average Running (Pro-Rated) Salaries over time