Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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, Pr...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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)

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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