Forum Discussion
YoY Change %
I am trying to write a measure and use it in a matrix that does two thing:
1. Show average values for a data point.
2. Claculate YoY % change of level above it.
I have an hierachy built as below. I nee to show Average values for Employee Name / Trade code and then show YoY Change % for all the above data fields.
I am not sure why the YoY Change % will not come out correct afor the top 3 levels. I am using this measure.
Billing Rate = Var _BR =Average(ProjectRateEscalation[BillingRate])
RETURN
if(ISINSCOPE(ProjectRateEscalation[Employee Name / Trade code]),_BR,
if(ISINSCOPE(ProjectRateEscalation[JobCode]),[YOY Change %],
if(ISINSCOPE(ProjectRateEscalation[control_job]),[YOY Change %],
if(ISINSCOPE(ProjectRateEscalation[OfficeCode]),[YOY Change %]))))
YOY Change % = Var PY = CALCULATE(AVERAGE(ProjectRateEscalation[BillingRate]),DATEADD('Table'[Date],-1,YEAR) )
Return DIVIDE(AVERAGE(ProjectRateEscalation[BillingRate])-PY,AVERAGE(ProjectRateEscalation[BillingRate]))
I am not sure why this measure would just not work. Can somebody please share some insight?
Below is the sample data :
| EmployeeNumber | TradeCode | TradeCodeDesc | EffectiveDate | Charging Rate | Employee Name / Trade code | Control Job | Year | JobCode | control_job | OfficeCode |
| 111 | 2/25/2023 | 79 | ABCD123 | 2023 | B22023-BOD | B22023-00 | 1B | |||
| 112 | 2/25/2023 | 165 | ABCD124 | 2023 | B22023-BOD | B22023-00 | 1B | |||
| 113 | 2/25/2023 | 235 | ABCD125 | 2023 | B23005-GR | B23005-00 | 1B | |||
| 114 | 2/25/2023 | 110 | ABCD126 | 2023 | B23005-GC | B23005-00 | 1B | |||
| 115 | 2/25/2023 | 146 | ABCD127 | 2023 | B23005-ZNR | B23005-00 | 1B | |||
| 116 | 2/25/2023 | 174 | ABCD128 | 2023 | B23005-GC | B23005-00 | 1B | |||
| 117 | GR3C | Group 3 Laborers - Con - Union | 1/1/2024 | 110 | Group 3 Laborers - Con - Union | ABCD129 | 2024 | B19022-GR | B19022-00 | 1B |
| 118 | G3FC | Group 3 Labor Foremn - Con - U | 1/2/2024 | 125 | Group 3 Labor Foremn - Con - U | ABCD130 | 2024 | B19022-GR | B19022-00 | 1B |
| 119 | 70C | 70% Appr Carp - Con - U | 1/2/2024 | 119 | 70% Appr Carp - Con - U | ABCD131 | 2024 | B19022-XCC | B19022-XCC | 1B |
| 120 | 1/1/2024 | 167 | ABCD132 | 2024 | B19022-XDD | B19022-XDD | 1B | |||
| 121 | 1/2/2025 | 84 | ABCD133 | 2025 | B19034-GCA | B19034-00 | 1B | |||
| 122 | 1/2/2025 | 118 | ABCD134 | 2025 | B19034-GCA | B19034-00 | 1B | |||
| 123 | 1/2/2025 | 122 | ABCD135 | 2025 | B19034-GCA | B19034-00 | 1B | |||
| 124 | DWA3 | Drywall- Appr- Level 3 - Non U | 12/24/2022 | 150 | Drywall- Appr- Level 3 - Non U | ABCD136 | 2022 | A23999-99 | A23999-00 | 4A |
| 125 | DRYA | DRYWALL APPRENTICE | 12/24/2022 | 78 | DRYWALL APPRENTICE | ABCD137 | 2022 | A23999-99 | A23999-00 | 4A |
| 126 | DRY1 | DRYWALLER NON UNION | 12/24/2022 | 236 | DRYWALLER NON UNION | ABCD138 | 2022 | A23999-99 | A23999-00 | 4A |
| 127 | CLTU | Crew Lead - Taper - Union | 12/24/2022 | 123 | Crew Lead - Taper - Union | ABCD139 | 2023 | A23999-99 | A23999-00 | 4A |
| 128 | CLTN | Crew Lead - Taper - Non Union | 12/24/2022 | 124 | Crew Lead - Taper - Non Union | ABCD140 | 2023 | A23999-99 | A23999-00 | 4A |
| 129 | CLLN | Crew Lead- Laborer- GC - Non U | 12/24/2022 | 212 | Crew Lead- Laborer- GC - Non U | ABCD141 | 2024 | A23999-99 | A23999-00 | 4A |
| 130 | CLLCU | Crew Lead - Laborer - Con - U | 12/24/2022 | 180 | Crew Lead - Laborer - Con - U | ABCD142 | 2025 | A23999-99 | A23999-00 | 4A |
1 Reply
Your sample data is a bit too sparse for a meaningful answer. But here is the general approach for a YoY% (without a proper Dates table)
