Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |