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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

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.

``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)
``````

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

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

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])``

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.

Community Support

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.

``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)
``````

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

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

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])``

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors