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

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

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

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

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021