Forum Discussion

CRanjan12's avatar
CRanjan12
New Member
2 years ago

Calculated Column to Retrieve previous date where value was different (Filtered by criteria)

Hi all, I am attempting to find previous last_run date where the position value was different in a records table based on the ID and product being the same. So following the table below, when the ...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

CRanjan12 See my article on Mean Time Between Failure (MTBF) which uses EARLIER. In this case, you would just need to stop at PreviousDate variable (see below). And of course include your ID and Product name in your filter.

 

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 - __Previous ) * 1.