Calculating Year over Year by ID

[jeggen]

I am looking for a way to calculate year-over-year trends for different categories but am not sure how to get started on this.

Here is an example of how this data works: (note: Sales1 and units are separate columns)

ID	Year	Sales1	Units
123	2018	50000	52
456	2018	35000	34
789	2018	25000	31
159	2018	22000	29
267	2018	37000	35
481	2018	51000	53
123	2019	52000	54
456	2019	37000	35
789	2019	24000	30
159	2019	21000	28
267	2019	33000	30
481	2019	55000	56
123	2020	35000	28
456	2020	39000	37
789	2020	25000	31
159	2020	23000	30
267	2020	29000	28
481	2020	57000	58

In this example I would like to be able to, as an example, calculate a multi-year growth/decline for each individual based on "ID." I would be okay with a % growht/decline for this year to last and/or something that would indicate two years of consecutive growth/delcine.

IF these were all on one line per ID I'd have a sense of what to do with a formulate. Since there is one line per ID/Year that's where I get stuck. This is the only way I can really get this data out of our system unless I pull in one data set per year.

Any suggestions on how I can calculate year-over-year with this kind of data?

[jeggen]

This is fantastic, thank you. Works great and provides YoY for any year that has a previous year's value available!

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

@jeggen 

you can try to create a column

Column = 
VAR _last=maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Year]=EARLIER('Table'[Year])-1),'Table'[Sales1    ])
RETURN IF(ISBLANK(_last),BLANK(),'Table'[Sales1    ]/_last-1)

pls see the attachment below

[amitchandak]

@jeggen , Create a seperate year table(Say Date)  with distinct year then create measures like

This Year = CALCULATE(sum('Table'[Sales1]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Sales1]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year]
diff % = divide([This Year]-[Last Year ],[Last Year])

[jeggen]

Would the table actually need to be one row per distinct ID? I'm trying to calculate Year over Year values for each unique ID.