Calculated column: Return last transaction date which is before a specific date for each client

[Anonymous]

Hi all,

I have two tables, 'transaction' and 'client'. The 'transaction' table have transaction date from 1 Jan 2016 to 31 Dec 2016.

In the 'client' table, I am trying to create a calculated colum to return the last transaction date which is before a specific date, say 30 June 2016, for each client.

Currently, I am using LOOKUPVALUE to return the last transaction date, =LOOKUPVALUE(transaction[date],transaction[date],LASTDATE(transaction[date]))

however I want to return the last transaction date which is before a specific date.

Thank you very much for your time in advance.

Best regards,

Eric

[TomMartens · Accepted solution]

Hey @Anonymous,

here you'll find a little pbix file

Please be aware that both tables "Client" and "Transactions" have a relationship: 'Client'[Client] (one-side) -> 'Transactions'[Client] (many-side).

This DAX-statement is used to create a calculated column in the Client table

Latest Transaction = 
CALCULATE(
    MAX('Transactions'[TransactionDate])
    ,'Transactions'[TransactionDate] < DATE(2016,7,1)
)

This DAX-statement can be used if there is no relationship between both tables

Latest Transaction = 
var currentClient = 'Client'[Client]
return
CALCULATE(
    MAX('Transactions'[TransactionDate])
    ,'Transactions'[TransactionDate] < DATE(2016,7,1) 
    ,'Transactions'[Client] = currentClient
)

Depending on the size of your tables there will be a noticeable advantage on performance for the 1st statement because it leverages the relationship.

Hope this is what you are looking for

Regards

Tom

[Anonymous]

Dear @TomMartens,

Thank you very much for your guidance.

In addition to getting the solution from you, I learned so much extra.

1. Using "calculate" function which leverage on the relationship will have advantage in term of performance. (I definitely need this)

2. Now I have better understanding in how to use "var" and "return", this can be used as well if there is no relationship is being built between tables.

Thank you very much again,

Eric