Forum Discussion
Second Last Value
Hello
I have successfully produced the last value using this calculation:
Last Value =
SUMX(FILTER('Market Prices','Market Prices'[Date]=LASTDATE('Market Prices'[Date])),'Market Prices'[Value])
Which gets used by:
Last Trans =
VAR Last_Date = Lastdate('Market Prices'[Date])
RETURN
CALCULATE([Last Value],
Filter(All('Market Prices'[Date]),
'Market Prices'[Date] = Last_Date))
But I am having trouble getting second last transaction (Transaction before last transaction).
Hi Anonymous ,
Here I suggest you to try below codes to create [Last Trans] and [Second Last Trans] measures.
Second Last Trans = VAR _Last_Date = MAXX ( FILTER ( ALL ( 'Market Prices' ), 'Market Prices'[Symbol] = MAX ( 'Market Prices'[Symbol] ) && 'Market Prices'[Date] < MAX ( 'Market Prices'[Date] ) ), 'Market Prices'[Date] ) RETURN CALCULATE ( SUM ( 'Market Prices'[Value] ), FILTER ( 'Market Prices', 'Market Prices'[Date] = _Last_Date ) )Last Trans = VAR _Last_Date = MAXX ( FILTER ( ALL ( 'Market Prices' ), 'Market Prices'[Symbol] = MAX ( 'Market Prices'[Symbol] ) ), 'Market Prices'[Date] ) RETURN CALCULATE ( SUM ( 'Market Prices'[Value] ), FILTER ( 'Market Prices', 'Market Prices'[Date] = _Last_Date ) )My Sample:
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
Here I suggest you to try below codes to create [Last Trans] and [Second Last Trans] measures.
Second Last Trans = VAR _Last_Date = MAXX ( FILTER ( ALL ( 'Market Prices' ), 'Market Prices'[Symbol] = MAX ( 'Market Prices'[Symbol] ) && 'Market Prices'[Date] < MAX ( 'Market Prices'[Date] ) ), 'Market Prices'[Date] ) RETURN CALCULATE ( SUM ( 'Market Prices'[Value] ), FILTER ( 'Market Prices', 'Market Prices'[Date] = _Last_Date ) )Last Trans = VAR _Last_Date = MAXX ( FILTER ( ALL ( 'Market Prices' ), 'Market Prices'[Symbol] = MAX ( 'Market Prices'[Symbol] ) ), 'Market Prices'[Date] ) RETURN CALCULATE ( SUM ( 'Market Prices'[Value] ), FILTER ( 'Market Prices', 'Market Prices'[Date] = _Last_Date ) )My Sample:
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.