Get total count 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-henryk-mstf · Accepted solution]

Hi @israabuhasna ,

According to the information you provided, I have done the following test as a reference: The created Total measure counts the grouping by PID, the row value corresponding to the ClassCode column in the current year, and finally sum counts the total value of the change in each year.

Total = 
VAR _curyear =
    MAX ( 'Table'[TaxYear] )
VAR _preyear =
    CALCULATE (
        MAX ( 'Table'[TaxYear] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[PID] = MAX ( 'Table'[PID] )
                && 'Table'[TaxYear] < _curyear
        )
    )
VAR _precc =
    CALCULATE (
        MAX ( 'Table'[ClassCODE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[PID] = MAX ( 'Table'[PID] )
                && 'Table'[TaxYear] = _preyear
        )
    )
VAR CurrentPrice =
    MAX ( 'Table'[ClassCODE] )
RETURN
    IF ( MAX ( 'Table'[ClassCODE] ) <> _precc && NOT ( ISBLANK ( _precc ) ), 1, 0 )

Measure = 
SUMX('Table',[Total])

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.

Best Regards,
Henry

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

[parry2k]

@israabuhasna what is the business rule for counting the code change, sorry it is not super clear

[israabuhasna]

Sorry for not making it clear in the original post:

If the code changes from any code to another during the 5 years then it counts as a code change. For example for one ID AX2:

AX2	2017	H2
AX2	2018	H2
AX2	2019	R3
AX2	2020	C5
AX2	2021	C5

The code changed from H2 to R3 in 2019 so it counts as 1 code change in 2019. Then in 2020 the code changed from R3 to C5 again counting 1 code change for 2020. So if we only had this single ID i would want the measure to show 1 for 2019 and 1 for 2020 while 0 for the other years.

If more explanation is needed, let me know.