Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to calculate date difference based on a column value

Hi, 

 

I have calculated the date difference between the start (Date of Entry) and End (Ticket Completion Time). 

I am wondering how I can get this value based on another column I have called Ticket status. I want it to only give me the date difference if the column value is completed and NOT working. 

 

BI TIme To Resolve Date Diff = DATEDIFF('ABF Unique Address Request form V1 (2)'[Date of Entry].[Date], 'ABF Unique Address Request form V1 (2)'[TicketCompletionTIme], DAY)
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , based on what I got

     

    BI TIme To Resolve Date Diff = if([Ticket status] in {Completed", "Not Working"} , DATEDIFF('ABF Unique Address Request form V1 (2)'[Date of Entry].[Date], 'ABF Unique Address Request form V1 (2)'[TicketCompletionTIme], DAY), blank())

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , based on what I got

     

    BI TIme To Resolve Date Diff = if([Ticket status] in {Completed", "Not Working"} , DATEDIFF('ABF Unique Address Request form V1 (2)'[Date of Entry].[Date], 'ABF Unique Address Request form V1 (2)'[TicketCompletionTIme], DAY), blank())

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      You got it! It worked! Thank you. Accepted as solution 

       

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

    Try

     

    BI TIme To Resolve Date Diff =
IF (
    'ABF Unique Address Request form V1 (2)'[Ticket status] <> "working" && 'ABF Unique Address Request form V1 (2)'[Ticket status] = "completed",
    DATEDIFF (
        'ABF Unique Address Request form V1 (2)'[Date of Entry].[Date],
        'ABF Unique Address Request form V1 (2)'[TicketCompletionTIme],
        DAY
    )
)