Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ramees_123
Helper IV
Helper IV

Calculated column to create the account status in case of multiple accounts

Hi, I have the below fields in my credit cards table. 

 

Customer ID

Card Open Date

Account Status

 

I need to create a new column Account Status Updated when a customer has more than one cards with Active status. If that happens then I need to check the maximum Card Open date and update only that has Active. Remaining I need to update as Closed.

 

In the below example, customer AE0001 has two cards with Active status. One card open date is 01-Jan-20 and another with 01-Feb-21. I need to make the 01-Feb-21 opened card as active since it is the latest date. Old cards need to be closed. 

 

The customer AE0002 also has two cards but it only one is Active. So it does not come to our case and so the same status is updated in Account Status Updated column too.

 

Please help.

 

Customer IDCard Open DateAccount StatusAccount Status Updated
AE000101-Jan-20ActiveClosed
AE000101-Feb-21ActiveActive
AE000201-Mar-20ActiveActive
AE000201-Mar-21ClosedClosed
AE000301-Mar-20ClosedClosed
AE000301-Mar-21ActiveActive
AE000401-Mar-21ActiveActive
AE000501-Mar-21ClosedClosed
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @Ramees_123 

 

You can try the following methods.

Account Status Updated = 
IF (
    CALCULATE (
        COUNT ( 'Table'[Account Status] ),
        FILTER (
            'Table',
            [Customer ID] = EARLIER ( 'Table'[Customer ID] )
                && [Account Status] = "Active"
        )
    ) > 1,
    IF (
        [Card Open Date]
            = CALCULATE (
                MIN ( 'Table'[Card Open Date] ),
                FILTER ( 'Table', [Customer ID] = EARLIER ( 'Table'[Customer ID] ) )
            ),
        "Closed",
        [Account Status]
    ),
    [Account Status]
)

The output you expect is shown in the figure.

vzhangti_0-1638515119394.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @Ramees_123 

 

You can try the following methods.

Account Status Updated = 
IF (
    CALCULATE (
        COUNT ( 'Table'[Account Status] ),
        FILTER (
            'Table',
            [Customer ID] = EARLIER ( 'Table'[Customer ID] )
                && [Account Status] = "Active"
        )
    ) > 1,
    IF (
        [Card Open Date]
            = CALCULATE (
                MIN ( 'Table'[Card Open Date] ),
                FILTER ( 'Table', [Customer ID] = EARLIER ( 'Table'[Customer ID] ) )
            ),
        "Closed",
        [Account Status]
    ),
    [Account Status]
)

The output you expect is shown in the figure.

vzhangti_0-1638515119394.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Ramees_123 , Try a new column like

 

New Status =
var _cnt = count(filter(Table,[Customer ID] =earlier([Customer ID]) && [Card Open Date] > earlier([Card Open Date]) && [Status] ="Active"),[Cusstomer ID])
return
if(isblank(_cnt), [Status], "Closed")

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.