cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 DateDelivery timescale (days)statusFuture delivery date (based on workdays and exclude bank holidays) ?
21/08/202030Large item 
16/07/202014small items 
11/09/202060customised item  

 

4 REPLIES 4
Highlighted
Super User IX
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?


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

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

@Greg_Deckler 

 

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
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 =
    ADDCOLUMNS(
      __Table1,
      "Rank",COUNTROWS(FILTER(__Table1,[Date]<=EARLIER([Date])))
    )
RETURN
  MAXX(FILTER(__Table2,[Rank]=__DeliveryDays),[Date])

Something along those lines.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

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

@Greg_Deckler 

 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors