Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

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

Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    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

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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