Year over year change in type string calculation

[apelleti]

I would like to create a measure that compares data from two different years (in a single column) and returns one of four types of change then plot the proportions of change type in a pie chart (i.e count of ID per change type)

Here is my problem:

I have data from two years appended into one table. I’m trying to compare the results from one year to the next and create a field that indicates the change for each row and assigns one of the following:

“no change – result 1”

“no change – result 2”
“change – increase”

“change – decrease”

The data types of “year” and “result” are text.

Here is my data structure:

ID	Year	Result
1	2021	1
2	2021	2
3	2021	1
4	2021	2
1	2022	1
2	2022	2
3	2022	2
4	2022	1

Results – measure

ID	YoY
1	No change – result 1
2	No change – result 2
3	Change – increase
4	Change – decrease

I then want to be able to summarize the measure and show counts of ID per YoY result.

Hoping someone could describe the best way to achieve this! 

[Barthel · Accepted solution]

Hey @apelleti,

Add an table visual and put the 'ID' in it.

Then create a measure that assigns the label to the 'ID'.

Measure = 
VAR _2022 =
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[Result] ),
        'Table'[Year] = "2022"
    )
VAR _2021 =
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[Result] ),
        'Table'[Year] = "2021"
    )
VAR _result =
    SWITCH ( 
        TRUE,
        _2022 = "1" && _2021 = "1", "No change – result 1",
        _2022 = "2" && _2021 = "2", "No change – result 2",
        _2022 = "2" && _2021 = "1", "Change – increase",
        _2022 = "1" && _2021 = "2", "Change – decrease"
    )
RETURN
    _result

Place the measure in the table visual for the desired result.

For your second request, we need to add a new table. I named this one 'Table (2)'.

YoY

No change – result 1

No change – result 2

Change – increase

Change – decrease

Place this new table in a table visual.

Create a second measure to calculate the number.

Measure 2 =
SUMX (
    'Table (2)',
    COUNTROWS (
        FILTER ( DISTINCT ( 'Table'[ID] ), [Measure] = EARLIER ( 'Table (2)'[YoY] ) )
    )
)

Finally, place the measure in the visual.

[apelleti]

Thank you @Barthel ! Works like a charm!