Row by Row comparison with dates

[TARANEESH]

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.

[Iamnvt]

hi,

@TARANEESH 

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:

https://1drv.ms/u/s!Aps8poidQa5zk65gLXMfq3QqkT5ZWQ?e=fWAQsB

[TARANEESH]

Hi,

@Iamnvt 

The solution is not working.

See if account_number is once terminated with date_terminated <>(not equal to) 31/12/9999,I want the account_status column (corresponding to a particular account_number in every row) should return "Terminated".

[Iamnvt]

@TARANEESH  in the PBI I attached. It gives exactly the expected result.

[TARANEESH]

Hi,

@Iamnvt 

I will recheck with original requirement (i.e as 5.5M records).

I am a begineer new to both Power Bi and DAX,thats why I can't figure out the issue.

There may be some error with records too.

Let you know if it works.

Thanks for your help.