cancel
Showing results for
Did you mean: 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. 1 ACCEPTED SOLUTION  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 (
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 (
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``````

4 REPLIES 4  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 (
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 (
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``````  Solution Sage

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. Regular Visitor

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]))))  Solution Sage  Announcements #### 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. #### 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

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
Top Kudoed Authors
Users online (1,919)