Calculation of Billing% per month

[Anonymous]

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.

[v-zhenbw-msft · Accepted solution]

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.

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)

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.