I am trying to get the last second date for a calculated column in a virtual table.

[amaan91]

@Greg_Deckler  
tried your code its returning error not sure wt i am doing wrong 

[amaan91]

The 

 VAR __LastDate = MAXX( Table_1, Append1[StatusDateTime] ) 

is retuning last date for Append1 table and not the Table_1 

[Greg_Deckler · Accepted solution]

@amaan91 You have 3 choices. You can create your table and then use the lookup code in a calculated column for that table or you can adapt the code to use ADDCOLUMNS or you can simply extend your SUMMARIZE statement like this:

My Table = SUMMARIZE('Table', [column1], [column2], [column3], "__LastDate", MAX('Table'[the date column])

[Greg_Deckler]

@amaan91 Maybe:

Measure = 
  VAR __Table = "My virtual table goes here"
  VAR __LastDate = MAXX( __Table, [Date] )
  VAR __SecondToLastDate = MAXX( FILTER( __Table, [Date] <> __LastDate ), [Date] )
RETURN
  __SecondToLastDate

[amaan91]

Hi @Greg_Deckler 

I am trying to get this within a virtual table 

ADDCOLUMNS(

SUMMARIZE( Append1 , Append1[CustomerName]  , Append1[Year] , Append1[Month No] ,  Append1[JobReference] , Append1[StatusDateTime] ,

 "Date" , LASTNONBLANK( Append1[StatusDateTime], 1) ,

" Accpted Rev" , [Accepted Sales.AG]  ,

"First Date" , CALCULATE( FIRSTNONBLANK(Append1[StatusDateTime] , 1 ) ,  ALLEXCEPT(Append1 , Append1[CustomerName])) ,

"Last Date" , CALCULATE(  LASTNONBLANK( Append1[StatusDateTime] , 1 ) ,  ALLEXCEPT(Append1 , Append1[CustomerName])) ,

//"Last Second" , CALCULATE( Max( TOPN( 2 , Append1 , Append1[StatusDateTime] , 0 ))),

"Period" ,  IF(   (Append1[StatusDateTime] - CALCULATE( FIRSTNONBLANK(Append1[StatusDateTime] , 1 ) ,  ALLEXCEPT(Append1 , Append1[CustomerName]))) / (365/12) <= 12 , 1 , 0  )  

 ) ,

 var Tab = SUMMARIZE( Append1 , Append1[CustomerName] ,  Append1[StatusDateTime])

  VAR __LastDate = MAXX( Tab , Append1[StatusDateTime]  )

  VAR __SecondToLastDate = MAXX( FILTER(  Tab  , [StatusDateTime] <> __LastDate ), [StatusDateTime] )

RETURN

  __SecondToLastDate

)

I am not able to refrence it