Total per month with contract dates

[Svenvdh]

Hi All,

I have a table with our employees, there montly salary and there FTE. The dates in this tabel is are the starting date and end date of the contracts. When the end date is empty it means the emloyee has a permanent contract. When an employee gets a new contract, his salary changes or the FTE changes the table gets a new line with a new start and end date for that employee.
I mananged to count the employees per month via a datetable and a Dax formula. But when I use this Dax formula for the FTE and the salary the employees that got changes will be summed up double in the month the changes are applicable. 

So I need a dax formula which would create a sum or total and also only counts the newest contract from that date per month.
I have below example with a simplified table, because I can't share the original table for the sensitive information this table has.

[daxer-almighty · Accepted solution]

# Employees =
var LastDateVisible = MAX ( 'Datetable'[Date] )
var FirstDateVisible = MIN ( 'Datetable'[Date] )
var Result =
    CALCULATE (
        DISTINCTCOUNT ( Contracts[Employee Number] ),
        KEEPFILTERS( 'Contracts'[Start] <= LastDateVisible ),
        KEEPFILTERS(
            COALESCE( 
                'Contracts'[End], 
                FirstDateVisible ) >= FirstDateVisible
        )
    )
return
    Result
    

Total FTE = 
var LastDateVisible = MAX ( 'Datetable'[Date] )
var FirstDateVisible = MIN ( 'Datetable'[Date] )
// For each employee we have to find
// the record that's the latest in
// the current time frame. The combination
// (Employee Number, Start) is unique.
var RelevantRecords =
    CALCULATETABLE (
        ADDCOLUMNS (
            DISTINCT ( Contracts[Employee Number] ),
            "@LatestRecordStartDate",
                CALCULATE (
                    MAX ( Contracts[Start] )
                )
        ),
        KEEPFILTERS ( 'Contracts'[Start] <= LastDateVisible ),
        KEEPFILTERS (
            COALESCE ( 
                'Contracts'[End], 
                FirstDateVisible ) >= FirstDateVisible
        )
    )   
var Result =
    CALCULATE (
        SUM ( Contracts[FTE] ),
        TREATAS (
            RelevantRecords,
            Contracts[Employee Number],
            Contracts[Start]
        )
    )
return
    Result


Total Salary = 
var LastDateVisible = MAX ( 'Datetable'[Date] )
var FirstDateVisible = MIN ( 'Datetable'[Date] )
// For each employee we have to find
// the record that's the latest in
// the current time frame. The combination
// (Employee Number, Start) is unique.
var RelevantRecords =
    CALCULATETABLE (
        ADDCOLUMNS (
            DISTINCT ( Contracts[Employee Number] ),
            "@LatestRecordStartDate",
                CALCULATE (
                    MAX ( Contracts[Start] )
                )
        ),
        KEEPFILTERS ( 'Contracts'[Start] <= LastDateVisible ),
        KEEPFILTERS (
            COALESCE ( 
                'Contracts'[End], 
                FirstDateVisible ) >= FirstDateVisible
        )
    )   
var Result =
    CALCULATE (
        SUM ( Contracts[Salary] ),
        TREATAS (
            RelevantRecords,
            Contracts[Employee Number],
            Contracts[Start]
        )
    )
return
    Result

[daxer-almighty]

@Svenvdh 

I don't think that a solution can be given without a detailed description of how the sum of FTE's and salaries should be calculated for different time periods. Also, it would be good to state what the model looks like. Well, at least I can't create a solution with this amount of information.

[Svenvdh]

As you can see in the picture with the example, I only have 1 database table [contracts]. And I have a date table.

Below formula I use for counting the employees per month. This works great.

Employees =

CALCULATE(DISTINCTCOUNT(Contracts[Employee Number]),

FILTER(VALUES('Contracts'[Start]), 'Contracts'[Start] <= MAX('Datetable'[Date])),

FILTER(VALUES('Contracts'[End]), OR( 'Contracts'[End] >= MIN(Datetable[Date]),ISBLANK('Contracts'[End]))))

The other formula  is the same to sum up the FTE. But I can't get the part where it only distinct the Employee number where the newest line is in that month.

FTE =

CALCULATE(SUMX(Contracts, Contracts[FTE]),

FILTER(VALUES('Contracts'[Start]), 'Contracts'[Start] <= MAX('Datetable'[Date])),

FILTER(VALUES('Contracts'[End]), OR( 'Contracts'[End] >= MIN(Datetable[Date]),ISBLANK('Contracts'[End]))))

[daxer-almighty]