Forum Discussion
Measure to return multiple values
- 3 years ago
Datafruit This is essentiall MTBF linked below, essentially:
previous transaction datetime column = VAR __curTrans = [transaction datetime] RETURN MAXX(FILTER('Table',[transaction datetime] < __curTrans),[transaction datetime]) and previous transaction datetime amount column = VAR __curTrans = [transaction datetime] VAR __prevTrans = MAXX(FILTER('Table',[transaction datetime] < __curTrans),[transaction datetime]) RETURN MAXX(FILTER('Table',[transaction datetime] = __prevTrans),[current transaction amount])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 - __Previous
Datafruit This is essentiall MTBF linked below, essentially:
previous transaction datetime column =
VAR __curTrans = [transaction datetime]
RETURN
MAXX(FILTER('Table',[transaction datetime] < __curTrans),[transaction datetime])
and
previous transaction datetime amount column =
VAR __curTrans = [transaction datetime]
VAR __prevTrans = MAXX(FILTER('Table',[transaction datetime] < __curTrans),[transaction datetime])
RETURN
MAXX(FILTER('Table',[transaction datetime] = __prevTrans),[current transaction amount])
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 - __Previous