Forum Discussion

Kev_Tord1's avatar
Kev_Tord1
Frequent Visitor
2 years ago
Solved

Calculated column Table - Dates

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' ...
Need Help
Kev_Tord1's avatar
Kev_Tord1
Frequent Visitor
2 years ago

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's avatar
ryan_mayu
Super User
2 years ago

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's avatar
    Kev_Tord1
    Frequent Visitor
    2 years ago

    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's avatar
      ryan_mayu
      Super User
      2 years ago

      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's avatar
        Kev_Tord1
        Frequent Visitor
        2 years ago

        Excellent thank you, please see the 3 tables:

        P_Employee

        Stakeholder IDMembership_SID
        A1Membership1
        B2Membership2
        C3Membership3

         

         

        P_Payroll

        Stakeholder IDDate
        A12022-07-10
        A12022-08-06
        A12022-09-03
        A12022-10-15
        A12023-04-01
        A12023-09-19
        A12023-09-29
        B22023-05-05
        B22023-08-12
        C32022-12-15
        C32023-10-15

         

        P_Employment

        Membership_SIDEffectivePay Rate
        Membership12022-07-1035860.5
        Membership12022-10-1237596
        Membership12023-03-2540989
        Membership12023-09-1945589.6
        Membership22022-10-1211000
        Membership22023-08-1018500
        Membership32022-12-1019000
        Membership32023-10-0123500

         

        Expected output through P_Payroll:

         

        Stakeholder IDDateCalc. column
        A12022-07-1035860.5
        A12022-08-0635860.5
        A12022-09-0335860.5
        A12022-10-1537596
        A12023-04-0140989
        A12023-09-1945589.6
        A12023-09-2945589.6
        B22023-05-0511000
        B22023-08-1218500
        C32022-12-1519000
        C32023-10-1523500

         

        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!