Most Recent Completed/Open Transaction Flag Per Store

[win_anthony]

The goal is to flag the most recent Completed or Open Transaction for each store filtering for a specific product code (product_code = CP - 545). My current output is successful at flagging the most recent Completed transaction for each store for a specific product code. The problem is that the current output will not recognize/flag the most recent Open transaction if it is not Completed for the store and specific product code. Below you will find: current Calculated Column DAX, Current Output sample data, Expected Output sample data

Any advice on how to update my calculated column to flag both the most recent Completed or Open transaction for each store filtering for a specific product code (product_code = CP - 545) will be greatly appreciated.

Current Calculated Column DAX

latest_complete_welcome_flag = 
var T1 = FILTER( Table, Table[store_id] = EARLIER( Table[store_id] ) )
RETURN
IF(
    CONTAINS( T1, Table[product_code], "CP - 545" ),
    IF( Table[Date] = MAXX( FILTER( T1, Table[status_bucket] = "Completed" && Table[product_code] = "CP - 545" ), Table[Date]), 1 ),
    IF( Table[Date] = MAXX( FILTER( T1, Table[status_bucket] = "Outstanding" && Table[product_code] = "CP - 545" ), Table[Date]), 1 )
)

Current Output sample data  (Notice Store_ID = 4 >>> does NOT flag for trans_id = 114 >> current Calculated Column DAX is missing this)

store_id	trans_id	product_code	product	status	status_bucket	date	latest_complete_welcome_flag
1	100	CP - 545	Welcome Letter	New	Outstanding	20-Oct-20
1	101	CP - 545	Welcome Letter	New	Outstanding	5-Jan-21
1	100	CP - 545	Welcome Letter	In Progress	Outstanding	6-Jan-21
1	100	CP - 545	Welcome Letter	Complete	Completed	7-Jan-21	1
2	102	CP - 452	Burger	New	Outstanding	1-Nov-20
2	103	CP - 528	Pizza	New	Outstanding	1-Nov-20
2	104	CP - 545	Welcome Letter	New	Outstanding	1-Nov-20
2	105	CP - 532	Wings	New	Outstanding	1-Nov-20
2	103	CP - 528	Pizza	Complete	Completed	2-Nov-20
2	104	CP - 545	Welcome Letter	In Progress	Outstanding	11-Nov-20	1
3	105	CP - 545	Welcome Letter	New	Outstanding	9-Feb-21
3	106	CP - 545	Welcome Letter	New	Outstanding	15-Feb-21
3	105	CP - 545	Welcome Letter	Complete	Completed	10-Feb-21
3	107	CP - 545	Welcome Letter	New	Outstanding	22-Feb-21
3	106	CP - 545	Welcome Letter	Complete	Completed	17-Feb-21	1
3	108	CP - 545	Welcome Letter	New	Outstanding	16-Feb-21
3	109	CP - 545	Welcome Letter	New	Outstanding	19-Feb-21
3	110	CP - 545	Welcome Letter	New	Outstanding	1-Mar-21
4	111	CP - 545	Welcome Letter	New	Outstanding	28-Feb-21
4	112	CP - 532	Wings	New	Outstanding	28-Feb-21
4	113	CP - 545	Welcome Letter	New	Outstanding	5-Mar-21
4	111	CP - 545	Welcome Letter	In Progress	Outstanding	1-Mar-21
4	114	CP - 545	Welcome Letter	New	Outstanding	20-Mar-21

Expected Output sample data (Notice Store_ID = 4 >>> YES flag for trans_id = 114 >> hoping to update current Calculated Column DAX to include this)

store_id	trans_id	product_code	product	status	status_bucket	date	latest_complete_welcome_flag
1	100	CP - 545	Welcome Letter	New	Outstanding	20-Oct-20
1	101	CP - 545	Welcome Letter	New	Outstanding	5-Jan-21
1	100	CP - 545	Welcome Letter	In Progress	Outstanding	6-Jan-21
1	100	CP - 545	Welcome Letter	Complete	Completed	7-Jan-21	1
2	102	CP - 452	Burger	New	Outstanding	1-Nov-20
2	103	CP - 528	Pizza	New	Outstanding	1-Nov-20
2	104	CP - 545	Welcome Letter	New	Outstanding	1-Nov-20
2	105	CP - 532	Wings	New	Outstanding	1-Nov-20
2	103	CP - 528	Pizza	Complete	Completed	2-Nov-20
2	104	CP - 545	Welcome Letter	In Progress	Outstanding	11-Nov-20	1
3	105	CP - 545	Welcome Letter	New	Outstanding	9-Feb-21
3	106	CP - 545	Welcome Letter	New	Outstanding	15-Feb-21
3	105	CP - 545	Welcome Letter	Complete	Completed	10-Feb-21
3	107	CP - 545	Welcome Letter	New	Outstanding	22-Feb-21
3	106	CP - 545	Welcome Letter	Complete	Completed	17-Feb-21	1
3	108	CP - 545	Welcome Letter	New	Outstanding	16-Feb-21
3	109	CP - 545	Welcome Letter	New	Outstanding	19-Feb-21
3	110	CP - 545	Welcome Letter	New	Outstanding	1-Mar-21
4	111	CP - 545	Welcome Letter	New	Outstanding	28-Feb-21
4	112	CP - 532	Wings	New	Outstanding	28-Feb-21
4	113	CP - 545	Welcome Letter	New	Outstanding	5-Mar-21
4	111	CP - 545	Welcome Letter	In Progress	Outstanding	1-Mar-21
4	114	CP - 545	Welcome Letter	New	Outstanding	20-Mar-21	1

[v-easonf-msft · Accepted solution]

Hi, @win_anthony 

Try to create calculated columns as below:

Sub_flag = 
VAR T1 =
    FILTER ( 'Table', 'Table'[store_id] = EARLIER ( 'Table'[store_id] ) )
RETURN
    // IF (
    //     CONTAINS ( T1, 'Table'[product_code], "CP - 545" ),
        IF (
            'Table'[Date]
                = MAXX (
                    FILTER (
                        T1,
                        'Table'[status_bucket] = "Completed"
                            && 'Table'[product_code] = "CP - 545"
                    ),
                    'Table'[Date]
                ),
            1,
            IF (
                'Table'[Date]
                    = MAXX (
                        FILTER (
                            T1,
                            'Table'[status_bucket] = "Outstanding"
                                && 'Table'[product_code] = "CP - 545"
                        ),
                        'Table'[Date]
                    ),
            2
            )
        )
    // )

Result_flag = 
VAR T1 =
    FILTER ( 'Table', 'Table'[store_id] = EARLIER ( 'Table'[store_id] ) )
RETURN
    IF (
        'Table'[Sub_flag]=MINX(
                    FILTER (
                        T1,
                        'Table'[product_code] = "CP - 545"
                    ),
                    'Table'[Sub_flag]
                ),
            1
    )

Result：

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.