Forum Discussion
Time difference for each Status in Power Bi
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!!
2 Replies
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/339586.
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 - __PreviousHi 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
