Diff between 2 values same column, based on value from another column

[powerbiuser101]

Hi,

I have n number of tickets. Each ticket has a created and changed date. Example:

Ticket	Create	Change	Desired Result
11	02.01.2018	02.02.2018	1
11	02.01.2018	02.04.2018	2
11	02.01.2018	02.08.2018	4

I want to filter based on [Ticket], and diff between the [Change] dates and input that into a [Desired Result] column. One catch is, the 1st value for each ticket uses [Create] - [Change] instead. 

Where I'm at, I used rankx to create a calculated column to rank the tickets. I'm thinking of creating another calculated column to then rank based on [Change]. And then somehow use this new column as a reference for the desired result. But this approach seems overly complex.

Alternatively, maybe I could diff between [Create] and [Change] for each row, and then do the subtraction with current row and previous row, filtered by [Ticket]? But I'm also unable to figure out the proper DAX expression for that. 

[Zubair_Muhammad · Accepted solution]

Hi @powerbiuser101

Try this Calculated Column

Desired Result =
VAR First_Date =
    CALCULATE (
        MIN ( TableName[Change] ),
        ALLEXCEPT ( TableName, TableName[Ticket] )
    )
VAR PreviousChange =
    CALCULATE (
        MAX ( TableName[Change] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Ticket] ),
            TableName[Change] < EARLIER ( TableName[Change] )
        )
    )
RETURN
    IF (
        TableName[Change] = First_Date,
        DATEDIFF ( TableName[Create], TableName[Change], DAY ),
        DATEDIFF ( PreviousChange, TableName[Change], DAY )
    )

[powerbiuser101]

Thanks @Zubair_Muhammad

It works beautifully! 

[Zubair_Muhammad]

@powerbiuser101