Calculating Monthly Salary

[kenneth0596]

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

[v-lili6-msft]

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]

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 · Accepted solution]

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