Compare two dates in different lines for same ID

[Krlos5411]

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!

[gmsamborn]

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

[gmsamborn · Accepted solution]

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]

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

[gmsamborn]

Hi @Krlos5411 

Can you post a screen-shot? 

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

[Krlos5411]

[gmsamborn]

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]

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.

[gmsamborn]

Hi @Krlos5411 

For use in a card would the following measure help?

Measure = 
    AVERAGEX(
        'Table',
        [Days from Created to Approval]
    )

[Krlos5411]

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]

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]

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]

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.