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

Calculation of Billing% per month

Hello Everyone

 

I have to create a forecast dashboard, where there are employee with some set of projects and have associated allocation start date and end date along with its billing% within this allocation period, so I need to calculate  billing% per month based on the working days.(for now excluding leaves and holidays)

 

Say eg:

                 Allocation Start date     Allocation End date          Billing

A   P1       20/6/2020                      15/10/2020                       60

     P2       26/9/2020                       30/11/2020                      40

 

actual working days of A       7+23+21+22+11

Total working days               22+23+21+22+22

 

Expected Output   2020        jun          jul               aug                sep              Oct
A       P1                            7/22*60     23/23*60      21/21*60      22/22*60      11/22*60

 


Please expecting a quick help.

Thanking you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Calculation of the billing percentage per month

Hello @ashwinil9203 ,

The working day doesn't calculate Saturday and Sunday?

If yes, we can create a working-day date table and perform some transformations in Power Query to meet your requirements.

1. We need to add rows between start date and end date.

cal1.jpg

cal2.jpg

cal3.jpg

2. Create a business day date table. Add the day column and the business day column.

Work Date = CALENDAR("2020/1/1","2020/12/31")

Day = FORMAT('Work Date'[Date],"dddd")
Month name = FORMAT('Work Date'[Date],"mmmm")
Month value = MONTH('Work Date'[Date])
work_day = 
SWITCH(
    TRUE(),
    'Work Date'[Day]="Monday",1,
    'Work Date'[Day]="Tuesday",1,
    'Work Date'[Day]="Wednesday",1,
    'Work Date'[Day]="Thursday",1,
    'Work Date'[Day]="Friday",1,0)

cal4.jpg

3. Add the day column, the work day column, and the month name column in the table.

cal5.jpg

4. Next, create a relationship between two tables based on the month name.

cal6.jpg

5. Finally, we can create a measure and use an array table to get the result.

Measure = DIVIDE(SUM('Table'[work_day]),SUM('Work Date'[work_day])) * AVERAGE('Table'[Billing])

cal7.jpg

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Calculation of the billing percentage per month

Hello @ashwinil9203 ,

The working day doesn't calculate Saturday and Sunday?

If yes, we can create a working-day date table and perform some transformations in Power Query to meet your requirements.

1. We need to add rows between start date and end date.

cal1.jpg

cal2.jpg

cal3.jpg

2. Create a business day date table. Add the day column and the business day column.

Work Date = CALENDAR("2020/1/1","2020/12/31")

Day = FORMAT('Work Date'[Date],"dddd")
Month name = FORMAT('Work Date'[Date],"mmmm")
Month value = MONTH('Work Date'[Date])
work_day = 
SWITCH(
    TRUE(),
    'Work Date'[Day]="Monday",1,
    'Work Date'[Day]="Tuesday",1,
    'Work Date'[Day]="Wednesday",1,
    'Work Date'[Day]="Thursday",1,
    'Work Date'[Day]="Friday",1,0)

cal4.jpg

3. Add the day column, the work day column, and the month name column in the table.

cal5.jpg

4. Next, create a relationship between two tables based on the month name.

cal6.jpg

5. Finally, we can create a measure and use an array table to get the result.

Measure = DIVIDE(SUM('Table'[work_day]),SUM('Work Date'[work_day])) * AVERAGE('Table'[Billing])

cal7.jpg

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors