Calculate Due Date based on day of week

bstmarie · January 18, 2022, 3:40pm

Hello! I have scoured the web for this answer and can’t seem to find what I am looking for. My current data is connected to our workforce management database and I am working on some analytics on if requests are put in on time. This would be an easier tasks if it was based on working days but we are wanting to look at a specific day.

I have included a screenshot of the pertinent data that I am using. I have a work start time (wostarttime) and a requested by date column (requestedbydate) and I am thinking that I can use these two to come up with a due date and then a calculated measure to see if the due date was met. The trick is that the requested by date due date should be the prior Wednesday to the start time - so essentially minus one week and on Wednesday. I have a date table so I know that I can reference day in week starting on Monday so the Wednesday should have a day of 3 but not sure how to combine the minus one week and set the day of week, any help is greatly appreciated!

cmalone1112 · January 18, 2022, 3:53pm

Could you mock up in Excel and post? I think you could do this in Power Query

bstmarie · January 18, 2022, 4:39pm

I was able to manipulate it enough for it to do what I want by using WEEKDAY and setting the start of the week to Wednesday. I am sure this is not the best way to do it but I get the results I want, I hope this is enough information
Clearance Request Dates.xlsx (10.2 KB)
.

EnterpriseDNA · January 20, 2022, 6:52pm

Bumping this post for more visibility.

deltaselect · January 21, 2022, 11:48pm

Hi @bstmarie ,

Please receive hereby a Power BI-workout, completely based upon your Excel formulas.
PBIX attached:
Requisted by and WO date comparison.pbix (98.6 KB)

Data sources are the provided Excel file and a standard date table, added are two calculated columns in “Workorders”, three measures and two basic graphs are added, see below:

The formatting of the date-columns within the table “Workorders” includes the full day name, for auditing purposes.

To calculate the target date, a Switch true function is used, added as calculated column.

Target date = SWITCH( true,
   WEEKDAY( Workorders[Start Date]) = 1, Workorders[Start Date] -4,  // Sunday WO start date , clearance request should be at least 4 days before, Wednesday
   WEEKDAY( Workorders[Start Date]) = 2, Workorders[Start Date] -5,  // Monday WO start date , clearance request should be at least 5 days before, Wednesday
   WEEKDAY( Workorders[Start Date]) = 3, Workorders[Start Date] -6,  // Tuesday WO start date , clearance request should be at least 6 days before, Wednesday
   WEEKDAY( Workorders[Start Date]) = 4, Workorders[Start Date] -7,  // Wednesday WO start date , clearance request should be at least 7 days before, Wednesday
   WEEKDAY( Workorders[Start Date]) = 5, Workorders[Start Date] -1,  // Thursday WO start date , clearance request should be at least 1 day before, Wednesday
   WEEKDAY( Workorders[Start Date]) = 6, Workorders[Start Date] -2,  // Friday WO start date , clearance request should be at least 2 days before, Wednesday
   WEEKDAY( Workorders[Start Date]) = 7, Workorders[Start Date] -3, // Saterday WO start date , clearance request should be at least 3 days before, Wednesday
  1) //1 = Error with date to be resolved by correcction date

To calculate the “requested on time”, a second calculated column is used in line with the Excel set up, this could also have been a measure.

Requested on time = IF(Workorders[Clearance Requested] <= Workorders[Target date], 1,0)

Three basic measures are set up in “DS Measures” to calculate the total workorders, the requested on time workorders and the percentage requested on time workorders.

Basic graphs could be made, as shown below:

I hope this helps you further,
kind regards, Jan van der Wind

EnterpriseDNA · January 24, 2022, 12:49am

Welcome @bstmarie to the forum

Good to see that you are having progress with your inquiry. Did the response from @deltaselect help you solve your inquiry?

If it does, kindly mark as solution the answer that solved your query.

If not, how far did you get and what kind of help you need further?

deltaselect · January 24, 2022, 3:09pm

Hi @bstmarie

New version2 with another way of calculating the target date with a measure:
Requisted by and WO date comparison v3.pbix (126.7 KB)

Just to give an alternative, hereby a measure, counting the Clearance Requested on Time:

Total WO requested on time = 
          sumx( Workorders, 
              if( (Workorders[Clearance Requested] <=  //clearance date
                  IF( WEEKDAY(Workorders[Start Date]) <= 4, Workorders[Start Date] - weekday(Workorders[Start Date])-3 ,Workorders[Start Date] - weekday(Workorders[Start Date])+4)), //target date (calcution)
                         1, 0))

P.S. Measures are preferable above calculated columns. In the attached PBIX the calculated columns are only left for reference purposes.

Kind regards,
Jan van der Wind

EnterpriseDNA · January 26, 2022, 12:30am

Hi @bstmarie, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

EnterpriseDNA · January 26, 2022, 8:00pm

Hi @bstmarie, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.