Total Last Year Calculation with Year Only

[E9067]

I have attempted several solutions from the forum and I am not achiving the desired result. I am attempting to calcuate the prior year value so that I can use it to calculate YOY % Change. Any help would be most appreciated. Below is sample data and calculations used thus far. Please note that originally I hade columns Report Year and Prior Year as integers. I converted these to dates with T_YEAR = DATE('Cars Processed Annually'[Report Year],1,1) to create a true date for referencing. I have attmepted with a date table and without a date table. Please, please help.

Report Year	Terminal Name	Cars Processed	Sum of CP	Report Year CP	PY CP 1	PY CP 2	PY CP 3	PY CP 4
2004	Location 1	814745	814745	814745		814745	814745	0
2005	Location 1	828365	828365	828365		828365	828365	0
2006	Location 1	829409	829409	829409		829409	829409	0
2007	Location 1	787052	787052	787052		787052	787052	0
2008	Location 1	748079	748079	748079		748079	748079	0
2009	Location 1	550835	550835	550835		550835	550835	0
2010	Location 1	652028	652028	652028		652028	652028	0
2011	Location 1	663821	663821	663821		663821	663821	0
2012	Location 1	655355	655355	655355		655355	655355	0
2013	Location 1	661510	661510	661510		661510	661510	0
2014	Location 1	647116	647116	647116		647116	647116	0
2015	Location 1	672801	672801	672801		672801	672801	0
2016	Location 1	676013	676013	676013		676013	676013	0
2017	Location 1	813219	813219	813219		813219	813219	0
2018	Location 1	891645	891645	891645		891645	891645	0
2019	Location 1	874786	874786	874786		874786	874786	0
2020	Location 1	907445	907445	907445		907445	907445	0
2021	Location 1	930621	930621	930621		930621	930621	0
2022	Location 1	931488	931488	931488		931488	931488	0
2023	Location 1	930651	930651	930651		930651	930651	0
2024	Location 1	187876	187876	187876		187876	187876	0

Below are expressions explaing the values above:

Cars Processed is the original aggrgated value created though a SQL group by function prior to pulling into PQE/PBI.

Sum of CP = CALCULATE(SUM('Cars Processed Annually'[Cars Processed])+0)

Report Year CP = SUMX('Cars Processed Annually','Cars Processed Annually'[Cars Processed])

PY CP 1 = CALCULATE(SUMX('Cars Processed Annually','Cars Processed Annually'[Cars Processed]), PREVIOUSYEAR('Cars Processed Annually'[T_YEAR]))

PY CP 2 =

VAR Cur_Year = SELECTEDVALUE('Cars Processed Annually'[T_YEAR])

RETURN

    CALCULATE(

        SUMX('Cars Processed Annually','Cars Processed Annually'[Cars Processed]),

        PREVIOUSYEAR('Corp Calendar'[Date])

    )

PY CP 3 =

VAR _currentyear = YEAR(TODAY())

VAR _table = FILTER(ALL('cars processed annually'), CONVERT('Cars Processed Annually'[Report Year],INTEGER) < _currentyear)

RETURN

SUMX('Cars Processed Annually','Cars Processed Annually'[Cars Processed])

PY CP 4 = CALCULATE([Sum of CP], SAMEPERIODLASTYEAR('Cars Processed Annually'[T_YEAR]))

[FreemanZ · Accepted solution]

hi @E9067 ,

Not sure if i really get you, try to add a calculated column like:

PY CP = 
VAR _currentyear = [Year]
VAR _result = 
MAXX(
    FILTER(data, data[Year]=_currentyear-1),
    data[CP]
)
RETURN _result

it worked like: