Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am a begginner when it comes to Power BI. This might be a simple question but I need to create a column within a date table that identifies the first working day of each month. I have already created a column that shows 1 for a working day and 0 for weekends and holidays. I just need the next step of created a column that shows 1 for the first working day of each month and for it to move to the next working day if the 1st of the month is a weekend or holiday.
The code I used for Working Days is this:
.IsWorkingDay = IF (NOT('Calendar'[DayOfWeek] = "Saturday" || ('Calendar'[DayOfWeek] = "Sunday")) && COUNTX(RELATEDTABLE('FEI Holidays'),1)<1,1,0)
Can you help?
Thanks!
Solved! Go to Solution.
Hi @Nsimpson
In my test, i create "holiday" table with the holiday date
assume my holiday date is 2018/1/1,2018/1/2,2018/1/3
then i create a related column in "calendar" table,
related = RELATED(holiday[Date])
Then i create the column
is working day = IF(NOT([day of week]=6&&[day of week]=7)&&'calendar'[Date]<>[related],1,0)
first working day = CALCULATE(MIN([Date]),FILTER(ALL('calendar'),'calendar'[year]=EARLIER('calendar'[year])&&'calendar'[month]=EARLIER('calendar'[month])&&[is working day]=1))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Nsimpson
In my test, i create "holiday" table with the holiday date
assume my holiday date is 2018/1/1,2018/1/2,2018/1/3
then i create a related column in "calendar" table,
related = RELATED(holiday[Date])
Then i create the column
is working day = IF(NOT([day of week]=6&&[day of week]=7)&&'calendar'[Date]<>[related],1,0)
first working day = CALCULATE(MIN([Date]),FILTER(ALL('calendar'),'calendar'[year]=EARLIER('calendar'[year])&&'calendar'[month]=EARLIER('calendar'[month])&&[is working day]=1))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Nsimpson
Create calculated columns
year = YEAR([Date]) month = MONTH([Date]) month = MONTH([Date]) first working day = CALCULATE(MIN([Date]),FILTER(ALL('calendar'),'calendar'[year]=EARLIER('calendar'[year])&&'calendar'[month]=EARLIER('calendar'[month])&&'calendar'[is working day]=1))
In my test, assume 2018/1/1, 2019/1/1 are holiday,
then for 2018/1, the firt working day is 2018/1/2
If you want to get 1/0 column to identify the first working day of each month,
create a column
Column = IF([first working day]=[Date],1,0)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This might work but I am receiving the circular dependency error when I try to create the First Working Day calc.
It has an issue with my .IsWorkingDay calulated column. Any suggestions to resolve this?
Thanks!
I also have the below measure and a holiday table. Not sure how I would add to skip over Holidays in the below code.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |