Forum Discussion

Jacqueline_Lim's avatar
Jacqueline_Lim
Regular Visitor
2 years ago
Solved

IF DAX for date function

Hi all,   I would like to look up the [LastTransactionDate] from 'Account' table, and if it's bigger than the [ReactivationDate] from 'DormantAccountReactivation' table, then return value with [Las...
  • Jacqueline_Lim's avatar
    Jacqueline_Lim
    2 years ago

    hey FreemanZ and Dangar332 ,

     

    Thanks for that! But I think I've finally figured it out.

     

    Below is my final DAX:

    First Transaction Date = IF(

    LOOKUPVALUE(Account[LastTransactionDate],'Account'[PositionAccountNo],'Dormant Account Reactivation'[ClientCode])>'Dormant Account Reactivation'[ReactivationDate],

    LOOKUPVALUE(Account[LastTransactionDate],'Account'[PositionAccountNo],'Dormant Account Reactivation'[ClientCode]),

    BLANK())

     

    My problem was, i have to pick up the LastTransactionDate column from another table and then compare it with the ReactivationDate column with my existing table, and return with either LastTransactionDate or BLANK.

     

    It contains 2 conditions: (1) lookup up value, (2) if condition.

    I just did this and the data looks fine to me. I hope it doesn't have any underliying issue that I haven't seen yet. *finger crossed*

Jacqueline_Lim's avatar
Jacqueline_Lim
Regular Visitor
2 years ago

FreemanZ , I've created an example, does it make sense?

 

Table 1:

ClientIDLastTransaction Date
130/10/2023
228/5/2021
39/6/2022
410/10/2023
521/12/2020

 

Table 2:

ClientIDReactivation Date
51/8/2023
42/8/2023
33/8/2023
24/8/2023
15/8/2023

 

Expected outcome:

ClientIDFirstTransactionDate
130/10/2023
2 
3 
410/10/2023
5 
FreemanZ's avatar
FreemanZ
Super User
2 years ago

or you create a calculated table like:

table = 
ADDCOLUMNS(
    VALUES(Table1[ClientID]),
    "Result",
    VAR _transactiodate = CALCULATE(MAX(Table1[LastTransaction Date]))
    VAR _reactivationdate = CALCULATE(MAX(Table2[Reactivation Date]))
    RETURN
        IF(_transactiodate>_reactivationdate, _transactiodate)
)

it worked like: