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

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