Determine Last State

[joshua1990]

Hello everyone!

I would like to determine the last tracked status within a specific systematic.

So, I have this table:

Date Requested	Item	Type1	Type2	Value	State	Date Finished	Order
01.01.2020	AAA1	2	AB	500	95		100
015.01.2020	AAA2	6	AC	600	100	01.02.2020	101

Now I would like to determine the last state for each order where "Type1" = 6 and "Type2" = AC.

How would you build that measure?

[stevedep]

Hi,

The below code should do the trick.

___GetLastState = 
var _maxdate = CALCULATE(MAX('Table'[Date Requested]), FILTER(ALL('Table'), 'Table'[Item] = SELECTEDVALUE('Table'[Item]) && 'Table'[Type1] = 6 && 'Table'[Type2] = "AC" ))
return
CALCULATE(MAX('Table'[State]), FILTER(ALL('Table'), 'Table'[Item] = SELECTEDVALUE('Table'[Item]) && 'Table'[Date Requested] = _maxdate))

Link to the file here. 

I recorded a video while creating the measure, hopefully, this helps in building an understanding of how to approach such a challenge. 

Please accept as a solution if so. Thumbs up for the effort is appreciated.

Kind regards, 

Steve. 

[CNENFRNL]

Hi, @joshua1990 , you may try this measure

Latest Status = MAXX( FILTER(SysLog, SysLog[Type1] = 6 && SysLog[Type2] = "AC"), SysLog[State] )