Look up a record from last day which is no longer present in latest load

[Joorge_C]

Hello All!

Im trying to look up closed transactions, the only thing is when they are closed they are no longer existent, so Im trying to figure out a way to look up past records and compare them to latest update of data and see which were closed.

thanks!!

[PattemManohar]

@Joorge_C  Please provide the sample data and your expected output to suggest you an accurate solution.

[Joorge_C]

Hello Thanks... so something like this. Transaction 1 and 2 where closed in Febuary since they are no longer existent.

I would need to count how many where closed on the last loaded date.

Loaded date	Transaction	Row
1/1/2019	123	1
1/1/2019	234	2
1/1/2019	345	3
2/1/2019	345	4

[PattemManohar]

@Joorge_C  To make it clear, the transactions 123 and 234 didn't apper on the latest Loaded Date i.e. 02/01/2019 and you want this count to be 2. Is that correct ? Do you want this as a new column ?

[Joorge_C]

Correct, you are right on!

[PattemManohar · Accepted solution]

@Joorge_C  Please try this as a New Column

MissingFlag = 
VAR _LatestDate = MAX(Test221PrevTransMissingCount[LoadDate])
VAR _Check = LOOKUPVALUE(Test221PrevTransMissingCount[Transaction],Test221PrevTransMissingCount[Transaction],Test221PrevTransMissingCount[Transaction],Test221PrevTransMissingCount[LoadDate],_LatestDate)
RETURN IF(ISBLANK(_Check) && Test221PrevTransMissingCount[LoadDate] <> _LatestDate,1,0)

You can then use the MissingFlag field to sum how many are missing compared to latest loaddate.

[Joorge_C]

MUCH APPRICIATED!