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 [LastTransactionDate], else blank.

 

My DAX is as below:

First Transaction Date = IF('Account'[LastTransactionDate]>'Dormant Account Reactivation'[ReactivationDate],'Account'[LastTransactionDate],blank())
 
But I received error as below:
A single value for column 'LastTransactionDate' in table 'Account' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

So I tried below DAX and error was resolved:

First Transaction Date = IF(MAX('Account'[LastTransactionDate])>MAX('Dormant Account Reactivation'[ReactivationDate]),MAX('Account'[LastTransactionDate]),blank())

 

But the return value was WRONG, it picked up the latest date in the LastTransactionDate column to compare with the latest date in the ReactivationDate column, and return with the latest date in the LastTransactionDate column, which turned out the whole First Transaction Date column became the same date.

 

Can anyone please help?

 
  • 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*

11 Replies

    • Jacqueline_Lim's avatar
      Jacqueline_Lim
      Regular Visitor
      2 years ago

      Hi FreemanZ ,

      It's a column. The 2 tables are related via unique client code.

      I'm hoping to see the table this way, the first transaction date will have data if the last transaction date is later than reactivation date.

       

      • FreemanZ's avatar
        FreemanZ
        Super User
        2 years ago

        hi Jacqueline_Lim 

        if you use the same code for a measure and plot the same table visual with the measure. it shall work, or?