Find value before last date

[Misq]

Hi all,

I need an answare for my question 😉 Looking for the way to get ItemNo for the date before the last date for ID and compare it to the last one.

So, right now my working measure to get the last ItemNo for last date for ID is looking as below (which is working good):

Last Item No= 
VAR __lastVisitDate = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) )

RETURN
CALCULATE ( MAX ( Table1[ItemNo ), ALLEXCEPT ( Table1, Table1[ID] ), Table1[Date] = __lastVisitDate )

Below is a sample of a table with data marked on green that I want to get:

id	date	ItemNo
1	1/1/2021	100
1	2/1/2021	200
1	3/1/2021	300
2	1/1/2021	100
2	2/1/2021	200
2	3/1/2021	300
2	4/1/2021	400

Is it somehow possiable?

Appriciate for all your help!

[tamerj1 · Accepted solution]

Hi @Misq 

please try

2nd Last Item No =
VAR IdTable =
    CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[ID] ) )
VAR LastTwoDatesTable =
    TOPN ( 2, IdTable, Table1[Date] )
VAR SecondLastDate =
    MINX ( LastTwoDatesTable, Table1[Date] )
RETURN
    MAXX (
        FILTER ( LastTwoDatesTable, Table1[Date] = SecondLastDate ),
        Table1[ItemNo]
    )