Forum Discussion
IF DAX for date function
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*
Hi Jacqueline_Lim ,
is the code for a measure or calculated column? How are the two mentioned table related?
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.
if you use the same code for a measure and plot the same table visual with the measure. it shall work, or?
FreemanZ ,
Which one?
What I need is, to lookup the LastTransactionDate in Account table, and check if it is later than the ReactivationDate in DormantAccountReactivation table, if it is, then return value as LastTransactionDate in Account table, else leave it as blank
This DAX gives me error:
First Transaction Date = IF('Account'[LastTransactionDate]>'Dormant Account Reactivation'[ReactivationDate],'Account'[LastTransactionDate],blank())I've tried creating for a new measure and a new column, both didn't work.
The error I've gotten is:
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.post some data and let us see how it shall work.
Post Sample Data
UPDATE: @ImkeF wrote a fantastic article for the best way to post data to the forums: https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-....