Forum Discussion
Calculated column Table - Dates
no need to modify the DAX, update the relationship between tables.
pls see the attachment below
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.
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.
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!
i create a measure , pls see the attachment below
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