Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
Change tag =
var _lastcode=
CALCULATE(MAX('Table'[ClassCODE]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))
return
IF(
[ClassCODE]<>_lastcode&&[Index]<>1,1,
0)
Measure =
SUMX(FILTER(ALLSELECTED('Table'),[TaxYear]=MAX('Table'[TaxYear])),[Change tag])
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.
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:
Change tag =
var _lastcode=
CALCULATE(MAX('Table'[ClassCODE]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))
return
IF(
[ClassCODE]<>_lastcode&&[Index]<>1,1,
0)
Measure =
SUMX(FILTER(ALLSELECTED('Table'),[TaxYear]=MAX('Table'[TaxYear])),[Change tag])
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.
Thank you! It worked like a charm for my problem. Much much appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |