Forum Discussion
Helper VFlagging Change in Data from Previous Month
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!
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)+1Current 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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)+1Current 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
newpbiuser01Thank you! that works beautifully!