Forum Discussion

Misq's avatar
Misq
Icon for Helper I rankHelper I
4 years ago
Solved

Find value before last date

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 l...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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]
    )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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]
    )