Identify the companies that have a profit for 4 or more successive months

[sunyangjun1115]

Hi Experts,

in DAX Studio, i  write below DAX to create a temp table :

DEFINE
VAR TempTable =

       DATATABLE(
           "CompanyCode", STRING,
           "YearMonth", STRING,
           "ProfitIndicator", INTEGER,
           {
               {"A", "202301", 1},
               {"A", "202302", 1},
               {"A", "202303", 1},
               {"A", "202304", 0},
               {"A", "202305", 1},
               {"A", "202306", 1},
               {"B", "202301", 1},
               {"B", "202302", 1},
               {"B", "202303", 1},
               {"B", "202304", 1},
               {"B", "202305", 1},
               {"B", "202306", 0}
           }
  
   )
 

the data in this table is below: 

CompanyCode	YearMonth	ProfitIndicator
A	202301	1
A	202302	1
A	202303	1
A	202304	0
A	202305	1
A	202306	1
B	202301	1
B	202302	1
B	202303	1
B	202304	1
B	202305	1
B	202306	0

my requiremnet is to idenitfy the companies which have a profit(ProfitIndicator =1) for 4 or more successive months.  as only company B has the profit for 5 successive months(202301/202302/202303/202304/202305),   the result should show company B. how to realize this by DAX ?  thanks in advance for your time and support.

[v-kaiyue-msft]

Hi @sunyangjun1115 ,

Thanks for the reply from Greg_Deckler , please allow me to provide another insight: 

1. Create the index column in the power query editor.

2. Create calculated columns.

Column = 
RANKX (
    FILTER (
        'Table',
        'Table'[ProfitIndicator] = 0
            && 'Table'[CompanyCode] = EARLIER ( 'Table'[CompanyCode] )
    ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)

Column 2 = 
IF (
    'Table'[ProfitIndicator] <> 0,
    RANKX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[CompanyCode] = EARLIER ( 'Table'[CompanyCode] )
                && 'Table'[Column] = EARLIER ( 'Table'[Column] )
        ),
        'Table'[YearMonth],
        ,
        ASC
    )
)

3. Create a calculation table.

FilteredCompanies = 
FILTER(
    GROUPBY(
        'Table',
        'Table'[CompanyCode],
        "MaxSuccessiveCount", MAXX(CURRENTGROUP(), [Column 2])
    ),
    [MaxSuccessiveCount] >= 4
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[sunyangjun1115]

@v-kaiyue-msft  , Many thanks for your great help.   it works if  i have the table  with 3 columns(Company Code/YearMonth/ProfitIndicator) that i showed as the example for my requirement. but actually, the third column "ProfitIndicator" is a measure , this measure is used to calculate if a company is profitable or not . 

seems that your method(using Power Query and create calculate column/Table) will not applicable for my requirement. 

thanks again for your time and great support.  

[Greg_Deckler]

@sunyangjun1115 Looks like an application for Cthulhu. Cthulhu - Microsoft Fabric Community

If this solution helped, please vote for my sticker! Banana Pickle Math - Microsoft Fabric Community

[sunyangjun1115 · Accepted solution]

@Greg_Deckler  , thanks so much for your prompt support.  i  tried the Cthulhu that you shared.  at the begining ,it's a bit difficult for me to understand how the DAX works.  afte tried in DAX studio for multiply times. now i can understand your DAX better.  

comparing the Cthulhu with my requirment,  it has some difference.  after  sometime's testing, i have already figured it out . below are the DAX code that i wrote in DAX Studio. 

DEFINE
    VAR TempTable =
        DATATABLE (
            "CompanyCode", STRING,
            "YearMonth", STRING,
            "ProfitIndicator", INTEGER,
            {
                { "A", "202301", 1 },
                { "A", "202302", 1 },
                { "A", "202303", 1 },
                { "A", "202304", 0 },
                { "A", "202305", 1 },
                { "A", "202306", 1 },
                { "B", "202301", 1 },
                { "B", "202302", 1 },
                { "B", "202303", 1 },
                { "B", "202304", 1 },
                { "B", "202305", 1 },
                { "B", "202306", 0 }
            }
        )
    VAR tempTable2 =
        ADDCOLUMNS (
            tempTable,
            "YearMonth_Indicator_0",
                MAXX (
                    FILTER (
                        tempTable,
                        [CompanyCode] = EARLIER ( [CompanyCode] )
                            && [YearMonth] < EARLIER ( [YearMonth] )
                            && [ProfitIndicator] = 0
                    ),
                    [YearMonth]
                )
        )
    VAR tempTable3 =
        ADDCOLUMNS (
            tempTable2,
            "IndexNo",
                IF (
                    [ProfitIndicator] = 0,
                    0,
                    IF (
                        [YearMonth_Indicator_0] <> BLANK (),
                        COUNTROWS (
                            FILTER (
                                tempTable2,
                                [CompanyCode] = EARLIER ( [CompanyCode] )
                                    && [YearMonth] >= EARLIER ( [YearMonth_Indicator_0] )
                                    && [YearMonth] <= EARLIER ( [YearMonth] )
                            )
                        ) - 1,
                        COUNTROWS (
                            FILTER (
                                tempTable2,
                                [CompanyCode] = EARLIER ( [CompanyCode] )
                                    && [YearMonth] <= EARLIER ( [YearMonth] )
                            )
                        )
                    )
                )
        )
    VAR result =
        GROUPBY ( FILTER ( tempTable3, [IndexNo] >= 4 ), [CompanyCode] )

EVALUATE
tempTable3

EVALUATE
result

[Greg_Deckler]

@sunyangjun1115 Glad you got it. In retrospect, I probably should have pointed you to Streaks! which is based on Cthulhu. Streaks! - Microsoft Fabric Community