Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Eric
Advocate I
Advocate I

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.