Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating Expected Due/Completion Date Time based on SLA & Created Date Time

Hi,

Looking for a help on DateTime Caculation.

I have a table with Unique Tasks Ids and its allocated dateTime and each of it has an SLA in days defined.

I need to create every Task's expected Completion date as per its SLA considering WorkingDays i.e. WeekDays.

The challenge is, eg. 2 Tasks can have same allocated Date Time with different SLA and the expected date time should have time precision. Eg. below;

Thanks !

Task IDAllocate DateTime

SLA Days

Expected_Completion DateTime
210024/01/2020 15:06127/01/2020 15:06
210124/01/2020 15:06531/01/2020 15:06
210228/01/2020 12:05230/01/2020 12:05

 

  • Anonymous's avatar
    Anonymous
    6 years ago

     

    QUOTIENT = QUOTIENT(WEEKDAY(Test[Allocate DateTime],1)+Test[SLA Days],7)
    Expected End Date = Test[Allocate DateTime]+Test[SLA Days]+2*Test[QUOTIENT]
     
    Thanks,
    Pravin

11 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Many thanks for your response Anonymous !

      I have tried that and the only issue is adding the Days to the Rank.

      In my case the value "10" is SLA and Dynamic coming from another Table, where the SLA for each datetime in the row.

      When I try to add it to the Rank (as the last parameter), it asks for aggrregation, where the output fails, since though the datetime it referes could be same but the SLA are different - (my table example above) !

       

      Appreciate your help on it ..

       

      Add 10 woring days = LOOKUPVALUE(Canlendar[Date],Canlendar[Identify],1,Canlendar[Rank1],Canlendar[Rank1]+10)

       

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

        try something like

        +SELECTEDVALUE(YourTable[SLA Days])

        do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Try this one as well

       

      New column=

      var Quotient_=QUOTIENT(WEEKDAY(Test[Allocate DateTime],1)+Test[SLA Days],7)

      return

      Expected End Date = Test[Allocate DateTime]+Test[SLA Days]+2*Quotient_
       
      Thanks & regards,
      Pravin Wattamwar
      www.linkedin.com/in/pravin-p-wattamwar

      If I resolve your problem Mark it as a solution and give kudos.