Time difference for each Status in Power Bi

[LuBS]

I have a date column, status column,  a reportID column, and other columns (not important for the calculation I am having trouble with) like this

reportID |  date  | Status

1             | date1 | Status 1

1             | date2 | Status 2

1             | date3 | Status 3

1             | date4 | Status 4

2             | date1 | Status 1

2             | date2 | Status 2

2             | date3 | Status 3

....

1000       | date 1000| status 1

I would like to get the time lapse in each status. taking tinto account each report ID. I am not sure how to tackle this. At first, I tried to unpivot columns based on the status, but that did not work. There are millions of reports some with 4 statuses others with more or less than.  I am sure there is another way. Please help. Thank you so much in advance!!

[Anonymous]

Hi @LuBS ,

You can create a calculated column as below to get it, please find the details in the attachment.

Column = 
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            'Table',
            'Table'[reportID] = EARLIER ( 'Table'[reportID] )
                && 'Table'[date] < EARLIER ( 'Table'[date] )
                && 'Table'[Status] <> EARLIER ( 'Table'[Status] )
        )
    )
RETURN
    DATEDIFF ( _predate, 'Table'[date], DAY )

Best Regards

[Greg_Deckler]

@LuBS See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous