Forum Discussion
Measure to return multiple values
Hi
I have a challenge and cannot seem to get over this one. I have a need to create a table, an example can be seen below.
So the idea is to display per id the transactions and current amounts and at the same time on the same row display previous transaction datetime and amount if such exists, hence the first row not having data in column 4 and 5.
Initially I tried with (below), but no luck. I tried bunch of different things, but none worked.
CALCULATE (
VALUES( 'table'[transaction started local]),
'table'[transaction type] = "FILL-OUT"
)
Any help you gurus can provide?
Thanks
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
1 Reply
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