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' 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!

Need Help

13 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

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

    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!

  • 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.