Calculate with primary key/secondary key for history tracking

[krampit1]

Hi all,

I´m facing some difficulties to get the right calculations in combination with history-tracking. 
Click here  to see my sample file.

Example:

Employee#1 (with primary key 1 and surrogate key 1001) has a ContractEndDate of 30-06-2021. He earns €10,00 per hour.
Employee#1 deserves a contract extension, so his ContractStartDate will be 01-07-2021 and ContractEndDate will be 31-12-2021. He earns with this contract €11,50 per hour.

Due to this situation I've added a new row with primary key 1 and surrogate key 1002.

Problem/desired result:
I would like to see the hours worked in the period of his first and second contract separately. This is important because of the €1,50 increase of hourly wage.

In my sample file the result of the first period should be 27 (=19 + 8 and the second period should be 32. (the values aren't realistic, because of a failure in my data transformation. The employee gets doubled when added a new row for him/her).

I appreciate all your help in advance.

[parry2k · Accepted solution]

@krampit1 try this measure:

Hours Total = 
SUMX ( 
    CROSSJOIN ( Employees, SUMMARIZE('Fact table', 'Fact table'[EmployeeID], 'Fact table'[Datum], 'Fact table'[HoursTotal] ) ), 
    IF ( 
        'Fact table'[Datum] >= Employees[ContractStartDate]  && 
        'Fact table'[Datum] <= Employees[ContractEndDate], 
        CALCULATE ( SUM ( 'Fact table'[HoursTotal] ) ) 
        )
)

and here is the output

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Greg_Deckler]

@krampit1 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365