Calculated column Table - Dates

[Kev_Tord1]

Hi everyone,

I was hoping someone could help me with this report. Essentially my report includes 2 tables.

X_Payroll' that summarizes the various pay periods for an employee and 

X_Employment' that includes "Pay Rate" with their effective date, for that same employee.

I would like to carry the matching "Pay Rate" to the associated "PP End Date". 

Note that effective dates don't match with the "PP End Date". The report would select the "Pay Rate" based on it's effective date. "PP End Date" would link to the most recent "Pay Rate" <=  PP End Date. (See example).

Another remark: If a field in X_Employment' table is empty, the previous most recent entry would apply. 

Please don't hesitate if you have any questions, and thanks in advance for any help!

[Kev_Tord1]

Hi @ryan ,

Thanks again for this, it works! As I had submitted a simplification of my current model, I was wondering if you had any idea of how to incorporate the notion of employee in the model. I have added a table to identify employees (Stakeholder ID) will link with the payroll table and Membership_SID will link with the membership with the employment table.

In fine, I am trying to capture the data by employees.

Thanks in advance!

[ryan_mayu]

can't see the whole picture, so can't provide the best soluiton. i think the easiest way is to combine the payroll and employment tables.

maybe you can proivde the sample data and the expected output

[Kev_Tord1]

Thanks Ryan, I've tried to attach the Pbix file in my early post but can't seem to find the option for file attachment? I was trying to avoid merging both Employment and Payroll tables given that tey are very heavy already. The output result would basically be the same than the one you provided except that it is now looking at several members records (different pay rate) and (different payroll dates). Let me attach the file as soon as I find the option (such a silly situation..) but thanks again for your time here, really.

[ryan_mayu]

then pls provide the sample data that you are using for the testing. provide the data of three tables. 

you can just paste the table in the reply. then let me know what's the expected output.

[Kev_Tord1]

Excellent thank you, please see the 3 tables:

P_Employee

Stakeholder ID	Membership_SID
A1	Membership1
B2	Membership2
C3	Membership3

P_Payroll

Stakeholder ID	Date
A1	2022-07-10
A1	2022-08-06
A1	2022-09-03
A1	2022-10-15
A1	2023-04-01
A1	2023-09-19
A1	2023-09-29
B2	2023-05-05
B2	2023-08-12
C3	2022-12-15
C3	2023-10-15

P_Employment

Membership_SID	Effective	Pay Rate
Membership1	2022-07-10	35860.5
Membership1	2022-10-12	37596
Membership1	2023-03-25	40989
Membership1	2023-09-19	45589.6
Membership2	2022-10-12	11000
Membership2	2023-08-10	18500
Membership3	2022-12-10	19000
Membership3	2023-10-01	23500

Expected output through P_Payroll:

Stakeholder ID	Date	Calc. column
A1	2022-07-10	35860.5
A1	2022-08-06	35860.5
A1	2022-09-03	35860.5
A1	2022-10-15	37596
A1	2023-04-01	40989
A1	2023-09-19	45589.6
A1	2023-09-29	45589.6
B2	2023-05-05	11000
B2	2023-08-12	18500
C3	2022-12-15	19000
C3	2023-10-15	23500

Here you go Ryan, let me know if you need anything else. In short, same approach as what you provided me with, except that now we have several members captured, and was hoping to have their respective salary records carried over to the Payroll table.

P_Employee has a relationship with P_Payroll on Employee ID, P_Employee has a relationship with P_Employment on Membership_SID.

This has given me headache for the last few days, really appreciate your help.

Thanks again!

[ryan_mayu]

i create a measure , pls see the attachment below

[Kev_Tord1]

Thanks Ryan, that seems to be correct, great! One question though, is it possible that it is not capturing the correct salary for the member B2 on 5/5/2023? I would have expected 11000, wouldn't it be the expected? Or maybe I'm missing something. But thanks so much

[ryan_mayu · Accepted solution]

no need to modify the DAX, update the relationship between tables. 

pls see the attachment below

[Kev_Tord1]

Late thank you @ryan_mayu , I was just able to try now and that is it. I'll do more testing based on my full model but this is just perfect! Thanks much!

[ryan_mayu]

you are welcome

[Kev_Tord1]

Hi @ryan_mayu , 

Thanks for sending this. I will give it a try. As I am still novice here, would you mind if I ask you for the actual PBi you used to lead to the result (if you have it handy). 

**Correction: I had missed that your working file was attached, thanks very much Ryan, will try now.

Thanks much!

[ryan_mayu]

you are welcome

[ryan_mayu]

@Kev_Tord1 

pls try this

Column =

VAR a=maxx(FILTER('x_employeement','x_employeement'[effective]=max('x_employeement'[effective])),'x_employeement'[pay rate])

VAR b=maxx(FILTER('x_employeement','x_employeement'[effective]<='x_payroll'[date]&&not(ISBLANK('x_employeement'[pay rate]))),x_employeement[effective])

return if('x_payroll'[date]>max('x_employeement'[effective]),a,maxx(FILTER(x_employeement,'x_employeement'[effective]=b),x_employeement[pay rate]))

pls see the attachement below