cancel
Showing results for
Did you mean:
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

Hi,

Hope this helps.

5 REPLIES 5
Super User

Re: Split Weekly Payroll Numbers into Daily Amounts

Hi,

Share a dataset and show the expected result.

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

Hi,

Hope this helps.

Regular Visitor

Re: Split Weekly Payroll Numbers into Daily Amounts

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

Highlighted
Super User

Re: Split Weekly Payroll Numbers into Daily Amounts

You are welcome.

Announcements

Community Highlights

Find out what's new in the Power BI Community!

Power Platform Summit North America

Register by September 5 to save \$200

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 370 members 4,075 guests
Recent signins: