Split Weekly Payroll Numbers into Daily Amounts

[Eric]

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.

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[Eric]

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 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Eric]

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

[Ashish_Mathur]

You are welcome.