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.
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.
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 |
---|---|
100 | |
99 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
106 | |
104 | |
85 | |
73 |