Count occurences with status change A to B for category

[soft5]

Dear Community,

Unfortunately I am lacking DAX fluency to fix the below formula and would like to ask for help.

The task is to count records of invoices that changed status from A to B.

My data sample:

Company	INVOICE_ID	Status	A/B	Date_2	Expected values
X	3681	open	A	4/7/2023	same
X	3681	in progress	A	4/11/2023
D	47163	in progress	A	4/7/2023	same
D	47163	failed	A	4/11/2023
Y	70050	failed	B	4/6/2023	same
Y	70050	failed	B	4/7/2023
X	79065	in progress	A	4/6/2023	1
X	79065	in progress	B	4/7/2023	0
X	79065	in progress	A	4/11/2023
X	80856	open	B	4/6/2023	0
X	80856	open	A	4/7/2023	same
X	80856	failed	A	4/11/2023
D	1a040	failed	A	4/7/2023	same
D	1a040	open	A	4/11/2023
X	22ac6	open	A	4/6/2023	1
X	22ac6	open	B	4/7/2023
X	3c3dc	failed	A	4/7/2023	same
X	3c3dc	open	A	4/11/2023
Y	467cc	open	A	4/7/2023	same
Y	467cc	in progress	A	4/11/2023
Y	4987a	open	A	4/7/2023	same
Y	4987a	failed	A	4/11/2023
C	5eefd	in progress	A	4/11/2023	same
X	5eefd	open	A	4/11/2023
X	83ab5	in progress	A	4/7/2023	1
X	83ab5	failed	B	4/11/2023
C	9c40e	open	A	4/7/2023	same
C	9c40e	in progress	A	4/11/2023
C	bcaef	failed	B	4/11/2023
Y	cba98	failed	A	4/6/2023	same
Y	cba98	failed	A	4/7/2023	same
Y	cba98	in progress	A	4/11/2023
X	d7b17	failed	A	4/7/2023	same
X	d7b17	open	A	4/11/2023
C	e0ad7	in progress	B	4/7/2023
Y	e42f6	in progress	A	4/6/2023	1
Y	e42f6	in progress	B	4/7/2023	0
Y	e42f6	open	A	4/11/2023
D	f7a73	failed	B	4/11/2023

I want to achieve the logic that is in column Expected values and then to count only "1"

like in excel formula =IF(B4=B5,IF(AND(D4="A",D5="B"), "1",IF(AND(D4="B",D5="A"),"0","same")),"")

I started this way ( no success;() :

StatusChangedA to B = var _cur_date = MAX(‘Table1’[Date_2])

var _pre_date = MAXX( FILTER( ALL(‘Table1’)  , ‘Table1’[Date_2]<_cur_date) ,[Date_2])

var _cur_list  = FILTER( ALL(‘Table1’) , ‘Table1’[Date_2] = _cur_date)

var _pre_list =  FILTER( ALL(‘Table1’) , ‘Table1’[Date_2] = _pre_date)

var _prevstat = LOOKUPVALUE(Table1[A/B],Table1[INVOICE_ID],_pre_list)

var _curstat =LOOKUPVALUE(Table1[A/B],Table1[INVOICE_ID],_cur_list)

var _t= INTERSECT(_pre_list,_cur_list)

return

COUNTROWS( FILTER(_t, AND (_prevstat= "A",_curstat="B")))

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

Expected values CC =
VAR _currentrow = Data[A/B]
VAR _nextrowdate =
    MINX (
        FILTER (
            Data,
            Data[INVOICE_ID] = EARLIER ( Data[INVOICE_ID] )
                && Data[Date_2] > EARLIER ( Data[Date_2] )
        ),
        Data[Date_2]
    )
VAR _nextrow =
    MAXX (
        FILTER (
            Data,
            Data[INVOICE_ID] = EARLIER ( Data[INVOICE_ID] )
                && Data[Date_2] = _nextrowdate
        ),
        Data[A/B]
    )
RETURN
    SWITCH (
        TRUE (),
        _currentrow = "A"
            && _nextrow = "B", "1",
        _currentrow = "B"
            && _nextrow = "A", "0",
        _currentrow = _nextrow, "Same",
        _nextrow = BLANK (), " "
    )

[soft5]

Thank you so much for help. It works fine.:)