I can’t find the correct content about this topic, so maybe this post is double. If have the following case. I would like to use the hourly rate between two dates and use this rate for the written hours.
In this case, when the hourly rate of a employee change from a certain date, it calculates with the new hourly reate. For example; the hourlyrate of employee x is between 01-01-2022 - 28-02-2022 € 50 and between 01-03-2022 - 31-12-2022 € 100
I re-added your tables with the Excel data you provided.
I created a list of each day that each employee was at specific rate, then created a unique key by merging the Employee ID and the new date field I created. This unique key field is also recreated in the hours table then a relationship created between them.
To create the list of dates that each employee is at a specific rate, I used some List functionality with the good olde { } curly brackets.
I wrap your [Startdate] column to give myself a starting point in a Number.From to convert the date to a number, so I close this part off {Number.From([Startdate]) and then add 2 full stops (this denotes my list function, well it tells Power Query/M that I’m creating a list between my first value and last value) and then create the end point of my list by adding …Number.From(Enddate])}.
Then I expand the list out and I have a list of each day the employee is at that specific rate, final steps are to convert the new column to a Date format and add the unique key.
Also, marked your Date Table as a Date Table and turned off the Auto/DateTime setting in the settings so you don’t get unwanted & unnecessary date tables lurking in the background.
Hi @Nick1, did the response provided by @DavieJoe help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
