topic Re: Identify rows where plan changed by ID and sameperiodlastyear in DAX Commands and Tips

Identify rows where plan changed by ID and sameperiodlastyear

Marvhall — Tue, 03 Sep 2024 17:19:09 GMT

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!

Re: Identify rows where plan changed by ID and sameperiodlastyear

ahadkarimi — Tue, 03 Sep 2024 19:13:26 GMT

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!

Re: Identify rows where plan changed by ID and sameperiodlastyear

Marvhall — Tue, 03 Sep 2024 19:27:42 GMT

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.

Re: Identify rows where plan changed by ID and sameperiodlastyear

Anonymous — Mon, 09 Sep 2024 06:43:25 GMT

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.