Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Rgds Adrian
Solved! Go to Solution.
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 week’s weekend or next week’s 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 week’s 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:
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.
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 week’s weekend or next week’s 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 week’s 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:
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.
@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
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |