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 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

     

Krlos5411's avatar
Krlos5411
Frequent Visitor
1 year ago

I got it in blank. I saw you example pbix, and it works but I think that is because you only are considering one ticket. I have several tickets with the calculation from start to end process. 

gmsamborn's avatar
gmsamborn
Icon for Super User rankSuper User
1 year ago

Hi Krlos5411 

 

Can you provide the following?

 

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

 

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 

3) Please explain how you would expect to get from step 1 to 2.

 

4) If possible, please show your past attempts at a solution.

 

 

A .pbix file with sample data would be best.

 

  • Krlos5411's avatar
    Krlos5411
    Frequent Visitor
    1 year ago

    Hi gmsamborn ,

     

    this is the link to my onedrive with the sample
    PBIX Sample

     

    3. What I'm lookinf for is to have an average of days that took from Create stage to Approval for all the tickets that has those stages. They are several, not just one.

     

    The first calculation that you sent, works for the calculations indeed, but it doesn't works if I need to have the total average of all the tickets.

     

    Thanks in advance for your assistance!!

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    1 year ago

    Hi Krlos5411 

     

    I added a measure that calculates the average.  Your [#Days] measure seems to work.

     

    Days = 
    AVERAGEX(
        'Table',
        [#Days]
    )

     

     

    Let me know if you have any questions.