cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Eric Regular Visitor
Regular Visitor

Split Weekly Payroll Numbers into Daily Amounts

I have a table with weely payroll amounts by person and I need to split those amounts into daily records for each person so I can accurate allocate costs to the appropriate month. 

 

We use a simple formula; M-F = 1.0, Sat = 0.5 and Sun =0 so each weekly amount needs to be spread over 5.5 days.  

 

How can I take each weekly total and create 6 daily records using the factors above?

 

I have a date table that also includes the paycheck date for each day if that helps any.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Split Weekly Payroll Numbers into Daily Amounts

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

5 REPLIES 5
Super User
Super User

Re: Split Weekly Payroll Numbers into Daily Amounts

Hi,

 

Share a dataset and show the expected result.

Eric Regular Visitor
Regular Visitor

Re: Split Weekly Payroll Numbers into Daily Amounts

Employee      Start Date        End Date        Check Date         Amount
John Doe       11/5/2018        11/11/2018      11/16/2018        $11,000.00
Bill White       11/5/2018        11/11/2018      11/16/2018          $5,500.00


Desired Result

Employee      Date         DayOfWeek      Amount
John Doe    11/5/2018          Mon            $2,000.00
John Doe    11/6/2018          Tue              $2,000.00
John Doe    11/7/2018          Wed            $2,000.00
John Doe    11/8/2018          Thu              $2,000.00
John Doe    11/9/2018          Fri                $2,000.00
John Doe    11/10/2018        Sat               $1,000.00
John Doe    11/11/2018        Sun             $       0.00


Bill White    11/5/2018         Mon            $1,000.00
Bill White    11/6/2018         Tue              $1,000.00
Bill White    11/7/2018         Wed            $1,000.00
Bill White    11/8/2018         Thu             $1,000.00
Bill White    11/9/2018         Fri               $1,000.00
Bill White    11/10/2018       Sat              $   500.00
Bill White    11/11/2018       Sun             $       0.00 

Super User
Super User

Re: Split Weekly Payroll Numbers into Daily Amounts

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

Eric Regular Visitor
Regular Visitor

Re: Split Weekly Payroll Numbers into Daily Amounts

Awesome!  Thanks Ashish, thus is exactly what I've been looking for.  

Super User
Super User

Re: Split Weekly Payroll Numbers into Daily Amounts

You are welcome.