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.
Hi,
I am stuck with this problem and I have no more ideas so any help will be greatly appreciated
Goal:
I have all the columns I need, except the last one. What I'm trying to create is the last column Week number based on 5 first working Days.
Note: In my project working day = only if day is no weekend, no Bank Holiday and no first or last day of month.
Logic for this column:
(IF WorkingDay (Y/N) column = "yes") then count first 5 working days and return as week 1. Then count another 5 working days and return 2 etc.
Exclude the first and the last day of the month from the calculations.
Of course, the calculation should be unique for each month, so I entered the YearMonthID column.
Date | YearMonthID | Weekend | BankHoliday | FirstORLastDayOfMonth | WorkingDay (Y/N) | Week number based on 5 first working Days |
01/04/2021 | 202104 | no | no | yes | no | |
02/04/2021 | 202104 | no | no | no | yes | 1 |
03/04/2021 | 202104 | yes | no | no | no | |
04/04/2021 | 202104 | yes | no | no | no | |
05/04/2021 | 202104 | no | yes | no | no | |
06/04/2021 | 202104 | no | no | no | yes | 1 |
07/04/2021 | 202104 | no | no | no | yes | 1 |
08/04/2021 | 202104 | no | no | no | yes | 1 |
09/04/2021 | 202104 | no | no | no | yes | 1 |
10/04/2021 | 202104 | yes | no | no | no | |
11/04/2021 | 202104 | yes | no | no | no | |
12/04/2021 | 202104 | no | no | no | yes | 2 |
13/04/2021 | 202104 | no | no | no | yes | 2 |
14/04/2021 | 202104 | no | no | no | yes | 2 |
15/04/2021 | 202104 | no | no | no | yes | 2 |
16/04/2021 | 202104 | no | no | no | yes | 2 |
17/04/2021 | 202104 | yes | no | no | no | |
18/04/2021 | 202104 | yes | no | no | no | |
... | ... | ... | ... | ... | ... | ... |
26/04/2021 | 202104 | no | no | no | yes | 4 |
27/04/2021 | 202104 | no | no | no | yes | 4 |
28/04/2021 | 202104 | no | no | no | yes | 4 |
29/04/2021 | 202104 | no | no | no | yes | 4 |
30/04/2021 | 202104 | no | no | yes | no |
Does anyone have suggestions for how to get the column values I need?
Thank you in advance,
Jack
Solved! Go to Solution.
You can count the number of working days in the month to date and then bucket those into multiples of 5.
WeekNumber =
VAR WorkdaysMTD =
CALCULATE (
COUNT ( Table1[Date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[YearMonthID] ),
Table1[Date] <= EARLIER ( Table1[Date] )
),
Table1[WorkingDay (Y/N)] = "yes"
)
RETURN
IF ( Table1[WorkingDay (Y/N)] = "yes", ROUNDUP ( WorkdaysMTD / 5, 0 ) )
You can count the number of working days in the month to date and then bucket those into multiples of 5.
WeekNumber =
VAR WorkdaysMTD =
CALCULATE (
COUNT ( Table1[Date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[YearMonthID] ),
Table1[Date] <= EARLIER ( Table1[Date] )
),
Table1[WorkingDay (Y/N)] = "yes"
)
RETURN
IF ( Table1[WorkingDay (Y/N)] = "yes", ROUNDUP ( WorkdaysMTD / 5, 0 ) )
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |