Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Add DateTime excluding weekends

Hi, I am trying to add a new column to create a Due Dates for the Ticket Created Date. But I have to exclude weekends. No idea on how to proceed.

 

Below is my requirement.

Columns:

Ticket ID, Priority, CreatedDate

 

Requirement - Create a new column as "Due Date"

Priority = 1 then no need to exclude weekends and add 2 hours to the Date value

Priority = 2 then exclude weekends and add 8 hours (Example: if Date is 22-Mar-2019 11:00 PM then Due Date should be 25-Mar-2019 7:00 AM

Priority = 3 then exclude weekends and add 5 business days to date value.

 

Please help...

 

1 ACCEPTED SOLUTION
PattemManohar
Community Champion
Community Champion

@Anonymous  Please try this as a New Column. Let me know how it goes and if it fails for any of your scenario. It will be always helpful if you can post some sample test data along with the expected output.

 

DueDate = 
VAR _P1 = Test274DateAddExcludeWeekends[CreatedDate] + TIME(2,0,0)
VAR _P2 = Test274DateAddExcludeWeekends[CreatedDate] + TIME(8,0,0)
VAR _P3 = Test274DateAddExcludeWeekends[CreatedDate] + 7
VAR _PreFinal1 = SWITCH(Test274DateAddExcludeWeekends[Priority],1,_P1,2,_P2)
VAR _PreFinal2 = SWITCH(WEEKDAY(_PreFinal1,2),6,_PreFinal1+2,7,_PreFinal1+1)
VAR _Final = IF(ISBLANK(_PreFinal2),_PreFinal1,_PreFinal2)
RETURN IF(Test274DateAddExcludeWeekends[Priority] IN {1,2},_Final,_P3)

image.png

 





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

Proud to be a PBI Community Champion




View solution in original post

9 REPLIES 9
PattemManohar
Community Champion
Community Champion

@Anonymous  Please try this as a New Column. Let me know how it goes and if it fails for any of your scenario. It will be always helpful if you can post some sample test data along with the expected output.

 

DueDate = 
VAR _P1 = Test274DateAddExcludeWeekends[CreatedDate] + TIME(2,0,0)
VAR _P2 = Test274DateAddExcludeWeekends[CreatedDate] + TIME(8,0,0)
VAR _P3 = Test274DateAddExcludeWeekends[CreatedDate] + 7
VAR _PreFinal1 = SWITCH(Test274DateAddExcludeWeekends[Priority],1,_P1,2,_P2)
VAR _PreFinal2 = SWITCH(WEEKDAY(_PreFinal1,2),6,_PreFinal1+2,7,_PreFinal1+1)
VAR _Final = IF(ISBLANK(_PreFinal2),_PreFinal1,_PreFinal2)
RETURN IF(Test274DateAddExcludeWeekends[Priority] IN {1,2},_Final,_P3)

image.png

 





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

Proud to be a PBI Community Champion




Anonymous
Not applicable

DueDate =
VAR _P1 = Test274DateAddExcludeWeekends[CreatedDate] + TIME(2,0,0)
VAR _P2 = Test274DateAddExcludeWeekends[CreatedDate] + TIME(8,0,0)
VAR _P3 = Test274DateAddExcludeWeekends[CreatedDate] + 7
VAR _PreFinal1 = SWITCH(Test274DateAddExcludeWeekends[Priority],1,_P1,2,_P2)
VAR _PreFinal2 = SWITCH(WEEKDAY(_PreFinal1,2),6,_PreFinal1+2,7,_PreFinal1+1)
VAR _Final = IF(ISBLANK(_PreFinal2),_PreFinal1,_PreFinal2)
RETURN IF(Test274DateAddExcludeWeekends[Priority] IN {1,2},_Final,_P3)

 

@PattemManohar Once again thank you much for helping me out. If I change VAR_P2 to Test274DateAddExcludeWeekends[CreatedDate] + 10 to skip 10 days excluding weekends. The weekends are not getting excluded. Please help..

 

@Anonymous  Just to confirm, you are trying the change the logic of P2 to add 10 days (Excluding Weekends) instead of 8 hours which we have done earlier. Is that correct ?





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

Proud to be a PBI Community Champion




Anonymous
Not applicable

@PattemManohar  Yes exactly. The first soloution worked perfectly for the Incident Ticket SLA. But now for Service Request ticket SLA we have to add P2 Created Date + 10 Days. But the weekends are not getting excluded. Please help..

@Anonymous  Thanks for confirming that. Here is the new logic considering the change to priority2 (adding 10 days excluding the weekends)

 

DueDateNew = 
VAR _P1 = Test274DateAddExcludeWeekends[CreatedDate] + TIME(2,0,0)
VAR _P2 = Test274DateAddExcludeWeekends[CreatedDate] + 14
VAR _P3 = Test274DateAddExcludeWeekends[CreatedDate] + 7
VAR _PreFinal = SWITCH(WEEKDAY(_P1,2),6,_P1+2,7,_P1+1)
VAR _Final = IF(ISBLANK(_PreFinal),_P1,_PreFinal)
RETURN SWITCH(Test274DateAddExcludeWeekends[Priority],1,_Final,2,_P2,3,_P3)

image.png

 

Note - In the above screenshot, DueDate is old logic and DueDateNew is the updated new logic.





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

Proud to be a PBI Community Champion




Anonymous
Not applicable

Thank you so much for your reply. I will try and update you the results.

 

I am very new to PBI . I tried to undertand the below lines and I have not clue on why we are adding _PreFinal1+2. Please explain so that I could understand.  Also how the weekends are getting excluded for P3. I dont see any special line written for that.

 

VAR _PreFinal2 = SWITCH(WEEKDAY(_PreFinal1,2),6,_PreFinal1+2,7,_PreFinal1+1)
VAR _Final = IF(ISBLANK(_PreFinal2),_PreFinal1,_PreFinal2)

 

SWITCH(Test274DateAddExcludeWeekends[Priority],1,_P1,2,_P2)  -  Is it possible to add one more condition like "Ticket Type" is "Incident" and Priority is 1 then _P1 in Switch.

 

Kindly help...

Anonymous
Not applicable

Extremly awesome. Especially the P3 logic. You saved my day...

@Anonymous Hope you have managed to test this ?

_Prefinal2  --> This is to skip the weekends, after adding the 2 hours or 8 hours based on the priority and the result date falls into weekend i.e saturday then +2 or sunday then +1

For P3, logically it is straight forward because for any weekday from Monday through Friday if you add 5 days then it will definitely fall into weekend. So it will be always +7 (5days + 2days for weekend)

Adding one more condition for ticket type then change the _PreFinal1 as below

VAR _PreFinal1 = IF([Priority] = 1&& [Type]="Incident",_P1,IF([Priority] = 2,_P2))

Note - It will be always helpful if you can post some sample test data and expected output.

 





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

Proud to be a PBI Community Champion




Greg_Deckler
Super User
Super User

See if my Net Work Days Quick Measure helps:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.