topic Re: I am trying to get the last second date for a calculated column in a virtual table. in DAX Commands and Tips

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

Anonymous — Fri, 15 Mar 2024 12:51:57 GMT

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

Greg_Deckler — Fri, 15 Mar 2024 14:01:00 GMT

Anonymous Maybe:

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

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

Anonymous — Tue, 19 Mar 2024 07:14:19 GMT

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

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

Anonymous — Tue, 19 Mar 2024 09:38:10 GMT

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

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

Anonymous — Tue, 19 Mar 2024 09:39:34 GMT

The

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

is retuning last date for Append1 table and not the Table_1

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

Greg_Deckler — Tue, 19 Mar 2024 16:34:07 GMT

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