Forum Discussion

Krlos5411's avatar
Krlos5411
Frequent Visitor
2 years 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 Help
  • gmsamborn's avatar
    gmsamborn
    2 years 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

     

gmsamborn's avatar
gmsamborn
Icon for Super User rankSuper User
2 years ago

Hi Krlos5411 

 

Can you post a screen-shot? 

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

Krlos5411's avatar
Krlos5411
Frequent Visitor
2 years ago

 

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

    Hi Krlos5411 

     

    The DAX I supplied is for a measure.  Exit from Power Query and create a new measure.

     

    Also, take a look at my pbix.

    • Krlos5411's avatar
      Krlos5411
      Frequent Visitor
      2 years ago

      It works. Thank you. 

      As I have the calculation of the days in a table and I need to have all the measures showed so the calculation can be done, do you know hoy can I have an average of those days in a separate card?  For example, this average is 2.19 and I want this in a separate card.