cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Svenvdh
Regular Visitor

Total per month with contract dates

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.

Svenvdh_1-1624463739509.png

 

 

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

 

 

# 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

 

 

View solution in original post

4 REPLIES 4
daxer-almighty
Solution Sage
Solution Sage

 

 

# 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

 

 

View solution in original post

daxer-almighty
Solution Sage
Solution Sage

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

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




daxeralmighty_1-1624535866476.png

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors