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*
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-....
FreemanZ , I've created an example, does it make sense?
Table 1:
| ClientID | LastTransaction Date |
| 1 | 30/10/2023 |
| 2 | 28/5/2021 |
| 3 | 9/6/2022 |
| 4 | 10/10/2023 |
| 5 | 21/12/2020 |
Table 2:
| ClientID | Reactivation Date |
| 5 | 1/8/2023 |
| 4 | 2/8/2023 |
| 3 | 3/8/2023 |
| 2 | 4/8/2023 |
| 1 | 5/8/2023 |
Expected outcome:
| ClientID | FirstTransactionDate |
| 1 | 30/10/2023 |
| 2 | |
| 3 | |
| 4 | 10/10/2023 |
| 5 |
how are table1 and table2 related?
hi Jacqueline_Lim ,
supposing they are related on ClientID column, then try to plot a table visual with Table1[ClientID] column and a measure like:
measure = VAR _transactiodate = MAX(Table1[LastTransaction Date]) VAR _reactivationdate = MAX(Table2[Reactivation Date]) RETURN IF(_transactiodate>_reactivationdate, _transactiodate, "")it worked like:
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
if your table 1 and table 2 are 1:1 then it might work
result1 = SWITCH(TRUE(),MAX(Table1[LastTransaction Date])>MAX(Table2[Reactivation Date]),MAX(Table1[LastTransaction Date]),"")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:
