Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
Please i need to quickly pick on someone brain for help.
I want to determine a future dates based on an agreed timescales in days only workdays( excluding weekend and bank holiday).
My result is counting weekends but i need to detrimine the dates by adding only the workdays (in this context delivery timescale).
any help wih be great
Order Date | Delivery timescale (days) | status | Future delivery date (based on workdays and exclude bank holidays) ? |
21/08/2020 | 30 | Large item | |
16/07/2020 | 14 | small items | |
11/09/2020 | 60 | customised item |
@OLADAODU Sounds like you want kind of the reverse of Net Work Days: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109
Do you have a date table with workday/not workday already flagged or no?
Thanks I do have a calendar table with workdays already calculated.
I have used that to claculated the workdays from the order date to current date but cant get around to determine the future date based on the timescales
@OLADAODU OK, so then you should be able to do this:
Column =
VAR __OrderDate = [Order Date]
VAR __DeliveryDays = [Delivery timescale (days)]
VAR __Table = FILTER('Calendar',[Date]>=__OrderDate && [Date]<=__OrderDate + __DeliveryDays * 2)
VAR __Table1 = FILTER(__Table,[Worday] = 1)
VAR __Table2 =
ADDCOLUMNS(
__Table1,
"Rank",COUNTROWS(FILTER(__Table1,[Date]<=EARLIER([Date])))
)
RETURN
MAXX(FILTER(__Table2,[Rank]=__DeliveryDays),[Date])
Something along those lines.
Thanks for your reply. i have been away.
Unfortunately I still cant determine what the date will be using this variable as the result is coming back as blank
I dont understand the rank you used and why the deliverydays variable multiple by 2.
I just need to know the exact date from today() if dilivery is due in 30 days excluding weekend and public holidays. considering my calendar_Table[Working days] =1
For example: Today() + 30 days = 3/11/2020
22/09/2020 + 30days = 3/11/2020 - this should be my result
rather than getting 22/10/2020
tips will be helpful
Thanks