Get total sum of Code change per ID in a year

[israabuhasna]

Hi. I have the below table

PID	TaxYear	ClassCODE
AX123	2017	I3
AX123	2018	I3
AX123	2019	I3
AX123	2020	C3
AX123	2021	C3
AX2	2017	H2
AX2	2018	H2
AX2	2019	R3
AX2	2020	C5
AX2	2021	C5
AX3	2017	O2
AX3	2018	O2
AX3	2019	I3
AX3	2020	C3
AX3	2021	C3
BX1	2017	C3
BX1	2018	H9
BX1	2019	C3
BX1	2020	C3
BX1	2021	C3
BX2	2017	H2
BX2	2018	H2
BX2	2019	H2
BX2	2020	H2
BX2	2021	C4

I would like to add a measure that calculates the total number of Code changes by year so I can add it to a bar chart with the year as an x axis and the sum of code changes as the values. For example Id like a measure that outputs:

2018	1
2019	3
2020	3
2021	1

I already have two measures that give me the current years Code and another that gives me last years code change but I want a sum of all code changes by year:

CodeChangeLY = 
  VAR 
    CurrentDate = MAX('TOT Tax Parcels'[TaxYear])
  VAR LastYear =
    CALCULATE( 
     MAX('TOT Tax Parcels'[ClassCode]), 
     'TOT Tax Parcels'[TaxYear] = CurrentDate-1,ALL('TOT Tax Parcels'[ClassCode])
    )
CurrentDate,ALL('TOT Tax Parcels'[ClassCode]))
  Return LastYear

CodeChangeCY = VAR 
    CurrentDate = MAX('TOT Tax Parcels'[TaxYear])
    VAR ThisYear = CALCULATE(MAX('TOT Tax Parcels'[ClassCode]),'TOT Tax Parcels'[TaxYear]  = CurrentDate,ALL('TOT Tax Parcels'[ClassCode]))
    Return ThisYear

[v-robertq-msft · Accepted solution]

Hi, @israabuhasna 

According to your description, you want to get the sum of the Code change group by year, you can try my steps:

This is my test data:

1. Create a calculated column:

Change tag =

var _lastcode=

CALCULATE(MAX('Table'[ClassCODE]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))

return

IF(

    [ClassCODE]<>_lastcode&&[Index]<>1,1,

0)

2. Create a measure:

Measure =

SUMX(FILTER(ALLSELECTED('Table'),[TaxYear]=MAX('Table'[TaxYear])),[Change tag])

3. Create a Matrix and place it like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[israabuhasna]

Thank you! It worked like a charm for my problem. Much much appreciated.