cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Calculate accurate Datediff to determine future dates based on timescales

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

4 REPLIES 4
Highlighted
Super User IX

Re: Calculate accurate Datediff to determine future dates based on timescales

@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?

---------------------------------------

I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Calculate accurate Datediff to determine future dates based on timescales

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

Highlighted
Super User IX

Re: Calculate accurate Datediff to determine future dates based on 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 =
__Table1,
"Rank",COUNTROWS(FILTER(__Table1,[Date]<=EARLIER([Date])))
)
RETURN
MAXX(FILTER(__Table2,[Rank]=__DeliveryDays),[Date])``````

Something along those lines.

---------------------------------------

I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Calculate accurate Datediff to determine future dates based on timescales

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

Thanks

Announcements

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021