Flagging Change in Data from Previous Month

[newpbiuser01]

Hello,

I have a table that lists the method of payment by a vendor and the month/year the transaction took place. 

Vendor	Month-Year	Method
A	2-2021	Debit Card
A	2-2021	Credit Card
B	3-2021	Debit Card
C	5-2021	Credit Card
D	1-2022	Credit Card
E	2-2022	Check
B	9-2023	Check
C	7-2021	Credit Card
D	11-2022	Debit Card
A	8-2023	Check
B	12-2022	Credit Card
C	7-2023	Debit Card
D	8-2021	Check
B	1-2023	Debit Card

I need to create a report that flags the method change each month by vendor. So for vendor A, first get the different methods used in the same month - so in February 2021 (2-2021), vendor A paid us through debit card and then by credit card once. So for that month, the method would be concatenated to be Credit Card, Debit Card.  

The next step would be to get the method for the previous month (1-2021), and get the method and then flag it they were the same or not. So the result would be:

Vendor	Month-Year	Previous Month-Year	Current Method	Previous Method	Flag
A	2-2021	1-2021	Debit Card, Credit Card		-
B	3-2021	2-2021	Debit Card	Debit Card, Credit Card	Change
C	5-2021	4-2021	Credit Card		-
D	1-2022	12-2021	Credit Card		-
E	2-2022	1-2022	Check	Credit Card	Change
B	9-2023	8-2023	Check	Check	Same
C	7-2021	6-2021	Credit Card		-
D	11-2022	10-2022	Debit Card		-
A	8-2023	7-2023	Check	Debit Card	Change
B	12-2022	11-2022	Credit Card	Debit Card	Change
C	7-2023	6-2023	Debit		-
D	8-2021	7-2021	Check	Credit Card	Change
B	1-2023	12-2022	Debit Card	Credit Card	Change

I think it has to be done in calculated columns because I need to calculate it for each row and flag each vendor record for each month/year. But, given that we have a large data set, I can't figure out how to do this. Would anyone know how I could do this?

Really appreciate any help!

[v-rzhou-msft · Accepted solution]

Hi @newpbiuser01 ,

I suggest you to convert [Month-Year] column as Date data type column and format it in MM-YYYY. This will make your calculation easier.

Measures:

Previous Month-Year = 
EOMONTH(MAX('Table'[Month-Year]),-2)+1

Current Method = 
CONCATENATEX('Table',[Method],", ")

Previous Method = 
CALCULATE (
    [Current Method],
    FILTER (
        ALL ( 'Table' ),
        'Table'[Month-Year]
            = EOMONTH ( MAX ( 'Table'[Month-Year] ), -2 ) + 1
    )
)

Flag = 
IF (
    [Previous Method] = BLANK (),
    "-",
    IF ( [Current Method] = [Previous Method], "Same", "Change" )
)

Result is as below.

Best Regards,
Rico Zhou

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

[newpbiuser01]

Thank you! that works beautifully!