Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
I wanted to add a column to populate the number of work days based on an existing date column, do you know how I get this working?E.g. for 01/14/2016, the month is Jan/2016 and there are 23 work days.
Thanks in advance,
INC111
Solved! Go to Solution.
Hi @inc111,
First, you need a calendar table. In below example, I specifcy Monthday as the first day of each week.
dim date = FILTER ( ADDCOLUMNS ( CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2016, 12, 31 ) ), "WeekDay", WEEKDAY ( [Date], 2 ) ), [WeekDay] <= 5 )
Then, add a calculated column in your fact table.
number of workday = CALCULATE ( COUNT ( 'dim date'[Date] ), FILTER ( ALL ( 'dim date' ), 'dim date'[Date].[Year] = YEAR ( EARLIER ( Table3[BillingDate] ) ) && 'dim date'[Date].[MonthNo] = MONTH ( EARLIER ( Table3[BillingDate] ) ) ) )
Best regards,
Yuliana Gu
Hi @inc111,
First, you need a calendar table. In below example, I specifcy Monthday as the first day of each week.
dim date = FILTER ( ADDCOLUMNS ( CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2016, 12, 31 ) ), "WeekDay", WEEKDAY ( [Date], 2 ) ), [WeekDay] <= 5 )
Then, add a calculated column in your fact table.
number of workday = CALCULATE ( COUNT ( 'dim date'[Date] ), FILTER ( ALL ( 'dim date' ), 'dim date'[Date].[Year] = YEAR ( EARLIER ( Table3[BillingDate] ) ) && 'dim date'[Date].[MonthNo] = MONTH ( EARLIER ( Table3[BillingDate] ) ) ) )
Best regards,
Yuliana Gu
Hey @v-yulgu-msft ,
Is it possible to get this to filter down to holidays? My compnay observes new years day so it would be 22 days in January. I'd also need to filter by all company holiday. Any help is appreciated.
In Power Query editor
1. Convert billingdate to date type
2. Add column
then day --> name of date
3. Add column as 2 but day --> day of week
4. Add a conditional column like if day of week is 5 or 6 (depence of locale settings) then "weekend" else "working day".
Is that you want???
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |