Forum Discussion

kenneth0596's avatar
kenneth0596
Frequent Visitor
6 years ago
Solved

Calculating Monthly Salary

Hi everybody,

 

My finance dept has a payroll calendar, there are two columns, one is cut off date and the other one is actual pay date. If anyone joined the company before the cut off date then he/she will get paid on pay date or else the employee will have to wait till next pay date.

 

What would be a clever way to calculate the amount of salary that Finance has paid at any given pay date? 

Any feedbacks would be greatly appreciated. Thank you! 

 

Cut off         Pay date

1/3/2020      1/15/2020

1/21/2020    1/31/2020

2/2/2020       2/13/2020

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi kenneth0596 

    You could create these three measure for it.

    Measure 2 = DATEDIFF(MAX('Table'[Join date]),MAX('payroll calendar'[Cut off]),DAY)
    Measure 3 = DATEDIFF(MAX('payroll calendar'[Cut off]),MAX('payroll calendar'[Pay date]),DAY)
    Measure 4 = IF([Measure 2]>0,IF([Measure 2]<[Measure 3],[Measure 2],[Measure 3]))
    (You could combine them into one formula)
    Then drag Emp field into visual too.
    Now, add a daily salary in the fact emp table that used it calculate salary by day.
     

    Regards,

    Lin

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    hi kenneth0596 

    You may try this simple logic

    Measure = CALCULATE(COUNTA('Table'[Emp]),FILTER('Table','Table'[Join date]<=MAX('payroll calendar'[Cut off])))
     
    Then just adjust COUNTA('Table'[Emp]) with salary.
    and here is my simple sample pbix file, please try it.
     
    Regards,
    Lin
    • kenneth0596's avatar
      kenneth0596
      Frequent Visitor
      6 years ago

      Hi Lin,

       

      Thank you for your response! May i ask you one more quesiton?

      Let's say we have two employees, A and B. A starts on 12/31/2019 and B starts on 1/13/2020.

       

      For the payroll period of 1/31/2020, A wouldve get the full payment and B would get paid less because he only works 8 days (1/21/2020 - 1/13/2020). How do we calculate the total # of days worked for each cycle? 

       

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        hi kenneth0596 

        You could create these three measure for it.

        Measure 2 = DATEDIFF(MAX('Table'[Join date]),MAX('payroll calendar'[Cut off]),DAY)
        Measure 3 = DATEDIFF(MAX('payroll calendar'[Cut off]),MAX('payroll calendar'[Pay date]),DAY)
        Measure 4 = IF([Measure 2]>0,IF([Measure 2]<[Measure 3],[Measure 2],[Measure 3]))
        (You could combine them into one formula)
        Then drag Emp field into visual too.
        Now, add a daily salary in the fact emp table that used it calculate salary by day.
         

        Regards,

        Lin