Forum Discussion

Krlos5411's avatar
Krlos5411
Frequent Visitor
1 year ago
Solved

Compare two dates in different lines for same ID

Hi All,

 

I need to calculate the time between the CREATE status and the APPROVAL status for every ticket that has those stages.

There's some tickets that has no APPROVAL status for example. So I need to check first if the ticket has the APPROVAL status and then, calculate the time between the two stages.

 

 

Thanks for your help!

 

Need Help
  • gmsamborn's avatar
    gmsamborn
    1 year ago

    Hi Krlos5411 

     

    Would a measure like this work?

     

    Days from Created to Approval = 
VAR _CurrStatus = SELECTEDVALUE('Table'[Status])
VAR _Create = 
    CALCULATE(
        MIN('Table'[Due Date]),
        ALL('Table'[Due Date]),
        'Table'[Status] = "CREATE"
    )
VAR _Approval = 
    CALCULATE(
        MAX('Table'[Due Date]),
        ALL('Table'[Due Date]),
        'Table'[Status] = "APPROVAL"
    )
VAR _Result = 
    IF(
        NOT ISBLANK(_Approval) 
            && _CurrStatus = "APPROVAL",
        DATEDIFF(_Create, _Approval, DAY)
    )
RETURN
    _Result

     

     

    Let me know if you have any questions.

     

    Days from Created to Approval.pbix

     

12 Replies

  • gmsamborn's avatar
    gmsamborn
    Super User
    1 year ago

    Hi Krlos5411 

     

    Would a measure like this work?

     

    Days from Created to Approval = 
VAR _CurrStatus = SELECTEDVALUE('Table'[Status])
VAR _Create = 
    CALCULATE(
        MIN('Table'[Due Date]),
        ALL('Table'[Due Date]),
        'Table'[Status] = "CREATE"
    )
VAR _Approval = 
    CALCULATE(
        MAX('Table'[Due Date]),
        ALL('Table'[Due Date]),
        'Table'[Status] = "APPROVAL"
    )
VAR _Result = 
    IF(
        NOT ISBLANK(_Approval) 
            && _CurrStatus = "APPROVAL",
        DATEDIFF(_Create, _Approval, DAY)
    )
RETURN
    _Result

     

     

    Let me know if you have any questions.

     

    Days from Created to Approval.pbix

     

    • Krlos5411's avatar
      Krlos5411
      Frequent Visitor
      1 year ago

      I got the error "Token Eof expected". Do you know what is this?

      • gmsamborn's avatar
        gmsamborn
        Super User
        1 year ago

        Hi Krlos5411 

         

        Can you post a screen-shot? 

        I'm pretty sure that the version I posted matches the measure in the pbix.

  • gmsamborn's avatar
    gmsamborn
    Super User
    1 year ago

    Krlos5411 

     

    After looking at your pbix again, I realized the measure I added already exists as [AVG days].

    In your visuals you should use the [AVG days] measure since it handles the rows as well as the average.

     

    I tried that measure and it works as far as I can tell.

     

    Are your sure the 2.43 measure ( [AVG Expected Result] ) is correct?