Create table to show if client had a successful transaction in the same month after payment failed

[Datagulf]

I have the following table.

Cust_ID		Date		transaction status		Month
1		01/01/2023		FAIL		Jan
1		02/01/2023		SUCCESS		Jan
2		02/01/2023		FAIL		Jan
3		02/01/2023		SUCCESS		Jan
4		03/01/2023		FAIL		Jan
1		01/02/2023		FAIL		Feb
1		02/02/2023		SUCCESS		Feb
2		01/02/2023		FAIL		Feb
3		02/02/2023		SUCCESS		Feb
4		01/02/2023		FAIL		Feb
4		02/02/2023		SUCCESS		Feb
5		02/02/2023		SUCCESS		Feb

I want to create a table that is as follows.(Expected output)

Cust_ID		Month	Successful Transaction
1		Jan	Yes
2		Jan	No
4		Jan	No
1		Feb	No
2		Feb	No
4		Feb	Yes

NB. 

All 1-time successful transactions for each client are not required in the output. 

For the months of January and February, we only have three transactions each in the output because they are either successful after a second trial or they failed completely.

All I am trying is to check if a client had a successful transaction in the same month after payment failed the first time. This is to decide if churn was decided by the number of failed transactions. How do I achieve that table? 

Thanks. 

[barritown · Accepted solution]

Hi @Datagulf, 

I am not sure it is the optimal solution to your problem, but it should work. Please try the [DAX] code below with your real data.

BTW, the test case seems to be not quite correct: with Cust_ID = 1 and Month = "Feb" you should have "yes" in the table.

In plain text:

Table = 
FILTER ( 
    ADDCOLUMNS ( 
        SUMMARIZE ( data, [Cust_ID], [Month] ),
        "Successful Transaction",
        VAR CurrentMonth = [Month]
        VAR CurrentID = [Cust_ID]
        VAR FailExists = IF ( COUNTROWS ( FILTER ( data, [Cust_ID] = CurrentID && [Month] = CurrentMonth && [transaction status] = "FAIL" ) ) = 0, 0, 1 )
        VAR MinFail = MINX ( FILTER ( data, [Cust_ID] = CurrentID && [Month] = CurrentMonth && [transaction status] = "FAIL" ), [Date] )
        VAR MaxSuccess = MAXX ( FILTER ( data, [Cust_ID] = CurrentID && [Month] = CurrentMonth && [transaction status] = "SUCCESS" ), [Date] )
        RETURN SWITCH ( TRUE(),
            FailExists = 0, BLANK(),
            MaxSuccess > MinFail, "Yes",
            "No" ) ), 
NOT ISBLANK ( [Successful Transaction] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian