Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Total Last Year Calculation with Year Only

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 YearTerminal NameCars ProcessedSum of CP Report Year CPPY CP 1PY CP 2PY CP 3PY CP 4
2004Location 1814745814745814745 8147458147450
2005Location 1828365828365828365 8283658283650
2006Location 1829409829409829409 8294098294090
2007Location 1787052787052787052 7870527870520
2008Location 1748079748079748079 7480797480790
2009Location 1550835550835550835 5508355508350
2010Location 1652028652028652028 6520286520280
2011Location 1663821663821663821 6638216638210
2012Location 1655355655355655355 6553556553550
2013Location 1661510661510661510 6615106615100
2014Location 1647116647116647116 6471166471160
2015Location 1672801672801672801 6728016728010
2016Location 1676013676013676013 6760136760130
2017Location 1813219813219813219 8132198132190
2018Location 1891645891645891645 8916458916450
2019Location 1874786874786874786 8747868747860
2020Location 1907445907445907445 9074459074450
2021Location 1930621930621930621 9306219306210
2022Location 1931488931488931488 9314889314880
2023Location 1930651930651930651 9306519306510
2024Location 1187876187876187876 1878761878760

 

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's avatar
    FreemanZ
    2 years ago

    hi Anonymous ,

     

    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:

     

1 Reply

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    hi Anonymous ,

     

    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: