Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Add to date (workdays (so exclude Sat & Sun), and exclude bank hols))

I have a simple table that is capturing holday bookings. I have the start date (&time) of the booking and the number of days & hours for each booking. When the days booked is greater than 1 day I need to calculate the end date (working day).

I have a separate table in the model with the bank holidays (Date & HolidayName) if needed. This is soooo easy in excel.

I know simlar questions have been asked but none seem to address this simple challenge.

Any Help would be Truly Appreciated (note data below is samlple not real)

 

AdruanTunley_0-1619532631969.png

Rgds  Adrian

1 ACCEPTED SOLUTION
v-yetao1-msft
Community Support
Community Support

Hi @Anonymous 

Please correct me if I wrongly understood your question.

According to your description, the returned value has three cases:

(1)If the value of 'Table'[WholeDays] is less than 1 , the End date can be blank;

(2)If the result of 'Table'[Start Date] +'Table'[WholeDays]+ 'Table'[HoursPlus] is this weeks weekend or next weeks working day, then the date will be postponed 2 days later .Because you said you need calculate the end date (working day).

(3)If the result of 'Table'[Start Date] +'Table'[WholeDays]+ 'Table'[HoursPlus] is this weeks working day , return the result directly .

I create a measure that may meet your needs :

End Date =

var _backdays=WEEKDAY(MAX('Table'[Start Date]),2)

var _backdays2=_backdays+SELECTEDVALUE('Table'[WholeDays])

var _EndDate=SELECTEDVALUE('Table'[Start Date])+SELECTEDVALUE('Table'[WholeDays])+SELECTEDVALUE('Table'[HoursPlus])

return IF(SELECTEDVALUE('Table'[WholeDays])<1,BLANK(),IF(_backdays2<6,_EndDate,_EndDate+2))

 

The effect is as shown:

Ailsa-msft_0-1619687592877.png

Notice : The data type of 'Table'[HoursPlus] must be Time

I have attached my pbix file, you can refer to it .

 

Best Regards

Community Support Team _ Ailsa Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yetao1-msft
Community Support
Community Support

Hi @Anonymous 

Please correct me if I wrongly understood your question.

According to your description, the returned value has three cases:

(1)If the value of 'Table'[WholeDays] is less than 1 , the End date can be blank;

(2)If the result of 'Table'[Start Date] +'Table'[WholeDays]+ 'Table'[HoursPlus] is this weeks weekend or next weeks working day, then the date will be postponed 2 days later .Because you said you need calculate the end date (working day).

(3)If the result of 'Table'[Start Date] +'Table'[WholeDays]+ 'Table'[HoursPlus] is this weeks working day , return the result directly .

I create a measure that may meet your needs :

End Date =

var _backdays=WEEKDAY(MAX('Table'[Start Date]),2)

var _backdays2=_backdays+SELECTEDVALUE('Table'[WholeDays])

var _EndDate=SELECTEDVALUE('Table'[Start Date])+SELECTEDVALUE('Table'[WholeDays])+SELECTEDVALUE('Table'[HoursPlus])

return IF(SELECTEDVALUE('Table'[WholeDays])<1,BLANK(),IF(_backdays2<6,_EndDate,_EndDate+2))

 

The effect is as shown:

Ailsa-msft_0-1619687592877.png

Notice : The data type of 'Table'[HoursPlus] must be Time

I have attached my pbix file, you can refer to it .

 

Best Regards

Community Support Team _ Ailsa Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , I have a file where I have used the date table to calculate workdays. This should be the same as your table with holiday marked

refer if the file attached after signature can help , check 2nd page /work day

 

I also created a video on same -https://www.youtube.com/watch?v=Qv4wT8_P-AA

Anonymous
Not applicable

Hi, the video refers to calculating date difference which is not what I am trying to do. I am trying to add days to a date (exluding Sat, Sun, & Public Holidays).  Do you have something more specif to adding days to a date?

Thx Adrian

PREVIEW

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.