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 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!

 

  • 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.

3 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.