Forum Discussion
Row by Row comparison with dates
Hi all,
I am beginner trying to understand Power BI and DAX.
I want help with the below issue.
For example,I have the following table in which Account_Number is repeated(i.e.Not unique) with date enroll and Termination.
Account_Number | Date_Enroll | Date_Terminated |
1 | 01-08-2019 | 04-08-2019 |
2 | 02-08-2019 | 06-08-2019 |
4 | 03-08-2019 | 05-08-2019 |
3 | 04-08-2019 | 07-08-2019 |
1 | 05-08-2019 | 10-08-2019 |
4 | 06-08-2019 | 31-12-9999 |
2 | 07-08-2019 | 31-12-9999 |
3 | 08-08-2019 | 05-08-2019 |
Scenario:
I want the new column (column-name:Account_Status) containing only two values (i.e. Open & Terminated).
Open----> for the accounts which has last date_terminated as 31-12-9999.
Terminated ----->for the accounts which has other last date_terminated.
Reuired Table:-
Account_Number | Date_Enroll | Date_Terminated | Account status |
1 | 01-08-2019 | 04-08-2019 | Terminated |
2 | 02-08-2019 | 06-08-2019 | Open |
4 | 03-08-2019 | 05-08-2019 | Open |
3 | 04-08-2019 | 07-08-2019 | Terminated |
1 | 05-08-2019 | 10-08-2019 | Terminated |
4 | 06-08-2019 | 31-12-9999 | Open |
2 | 07-08-2019 | 31-12-9999 | Open |
3 | 08-08-2019 | 05-08-2019 | Terminated |
Please help me with this. Thanks in advance.
4 Replies
hi,
you can try this measure:
Account Status = VAR __lastdate = CALCULATE(MAX('Table'[Date_Terminated]), ALLEXCEPT('Table', 'Table'[Account_Number])) RETURN IF(__lastdate = "31/12/9999", "Open", "Terminated")here is the PBI file:
