Identify rows where plan changed by ID and sameperiodlastyear

[Marvhall]

Good afternoon,

I need to identify the rows that have the same ID, plan A in the later year and a different plan sameperiodlastyear.

Below is an example:

Table A (what I have) is a comprehensive list of all IDs, plans, and dates. I need Table B where I only get the two (if exist) ID where the later date was plan A and a different plan sameperiodlastyear.

I've tried several formulas and can get close but it won't list only the two rows I need per ID (if exist under the above conditions).

Thank you in advance!

[v-jiewu-msft · Accepted solution]

Hi @Marvhall ,

Based on my testing, please try the following methods:

1.Create the new column to filter the changed plan.

SamePeriodLastYear = 
       CALCULATE(
           MAX('Table A'[Plan]),
           FILTER(
               'Table A',
               ('Table A'[ID] = EARLIER('Table A'[ID]) &&
               YEAR('Table A'[Date]) = YEAR(EARLIER('Table A'[Date])) - 1 &&
               MONTH('Table A'[Date]) = MONTH(EARLIER('Table A'[Date])) && EARLIER('Table A'[Plan]) = "A")
           )
       )

2.Create the new column to filter the rows.

Plan changed = 
var _sameperiod = VALUES('Table A'[SamePeriodLastYear])
var _date = SELECTEDVALUE('Table'[Date])
RETURN
IF(
    ('Table A'[Plan] = "A" && 'Table A'[Date] = DATE(2024, 7 ,1)) || ('Table A'[Plan] IN _sameperiod && 'Table A'[Date] = DATE(2023,7,1)),
    1,
    0
)

3.Create new table.

Table B = FILTER('Table A', 'Table A'[Plan changed] = 1)

4.Table B is shown below.

Best Regards,

Wisdom Wu

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

[ahadkarimi]

Hi @Marvhall, give calculated table a try, and if you encounter any issues, let me know.

create a calculated table:

TableB = 
VAR CurrentPeriod = 
    SELECTCOLUMNS(
        FILTER('TableA', 'TableA'[Date] = DATE(2024, 7, 1)),
        "ID", 'TableA'[ID],
        "Plan", 'TableA'[Plan],
        "Date", 'TableA'[Date]
    )

VAR PreviousPeriod = 
    SELECTCOLUMNS(
        FILTER('TableA', 'TableA'[Date] = DATE(2023, 7, 1)),
        "ID", 'TableA'[ID],
        "Plan", 'TableA'[Plan],
        "Date", 'TableA'[Date]
    )

RETURN
    FILTER(
        NATURALINNERJOIN(CurrentPeriod, PreviousPeriod),
        CurrentPeriod[Plan] = "A" &&
        PreviousPeriod[Plan] <> "A"
    )

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

[Marvhall]

Thank you. I get the error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. Same issue I ran into before. I tried using sumx or another aggregate but that didn't work either.