Creating an active column that takes the 3 most recent values

[Majad_Chowdhury]

I have a dataset that holds data similar to the following:

Id YearWeekValue Stage

1	202407	Won
1	202406	Won
1	202405	Won
2	202407	Lost
2	202406	Negotiation
2	202405	Lead

I want to create a column that marks an ID as Active or inactive based on this condition:

For each ID, if the 3 highest YearWeekValue are all the same and either "Won", or "Lost" , then mark as "Inactive" otherwise mark as "Active"

So in the aboe example ID = 1 should be marked as Inactive and ID = 2 should be marked as Active

[v-weiyan1-msft · Accepted solution]

Hi @Majad_Chowdhury ,

Based on the sample and description you provided, Please try code as below to create a Calculated column.

My Sample:

Marked = 
VAR Current_Id = 'Table'[Id]
VAR Dis_Count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Stage] ),
        FILTER (
            'Table',
            'Table'[Id] = Current_Id
                && RANKX (
                    FILTER ( 'Table', 'Table'[Id] = EARLIER ( 'Table'[Id] ) ),
                    'Table'[Value],
                    ,
                    DESC
                ) <= 3
        )
    )
VAR MaxStage =
    MAXX (
        FILTER (
            'Table',
            'Table'[Id] = Current_Id
                && RANKX (
                    FILTER ( 'Table', 'Table'[Id] = EARLIER ( 'Table'[Id] ) ),
                    'Table'[Value],
                    ,
                    DESC
                ) = 1
        ),
        'Table'[Stage]
    )
RETURN
    IF (
        Dis_Count = 1
            && ( MaxStage = "Won"
            || MaxStage = "Lost" ),
        "Inactive",
        "Active"
    )

Result is as below.

Best Regards,
Yulia Yan

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

[Majad_Chowdhury]

Thank You. This worked. No idea whats going on but it has done the job!