Forum Discussion

Misq's avatar
Misq
Helper 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 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:

iddateItemNo
11/1/2021100
12/1/2021200
13/1/2021300
21/1/2021100
22/1/2021200
23/1/2021300
24/1/2021400

 

Is it somehow possiable?

 

Appriciate for all your help!

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

1 Reply

  • tamerj1's avatar
    tamerj1
    Community 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]
    )