Forum Discussion

Marvhall's avatar
Marvhall
Icon for Helper I rankHelper I
2 years ago
Solved

Identify rows where plan changed by ID and sameperiodlastyear

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 com...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
ahadkarimi
Icon for Solution Specialist rankSolution Specialist
2 years ago

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's avatar
Marvhall
Icon for Helper I rankHelper I
2 years ago

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.