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

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 272 members 2,774 guests
Please welcome our newest community members: