How to find YOY only with Year column and date column in dataset

[nkaruturi]

HI,

I have a dataset where I dont have date column but have data year by year. I know to caclculate YOY with date column, but with out date column how can we calculate YOY is something I am not sure.  I tried to create a date table but that did not work.

DateSet I have is 

Company	Year	Sales
Company ABC	2017	1000
Company ABC	2018	750
Comapny ABC	2018	750
Company XYZ	2017	2000
Company XYZ	2018	1000
GEFF	2017	3000
GEF	2018	100
GEFF	2018	100

I want to show in Matrix visualizations. Below is the sample output

Year  Company	2017 Sales Amount	2017 Sales Variance	2018 Sales Amount	2018 Sales Variance
Comapny ABC	1000	0	1500	500
Company XYZ	2000	0	1000	-1000
GEFF	3000	0	200	-2800

[nkaruturi · Accepted solution]

was able to get using the way explained in below link

https://stackoverflow.com/questions/52538481/year-over-year-variance-calculation-error-earlier-earli...

[Anonymous]

Try this
First you need to have a date table
In your date_dim add these two columns

START of year  = STARTOFYEAR('Date_dim'[Date])

Year Rank = RANKX(allnonblankrow('Date_dim'),'Date_dim'[START of YEAR],,ASC,Dense)

Now create these three measures

Last year sale = Calculate(sum('table'[sale]),filter(all(date_dim),'date_dim'[Year Rank] = max('date_dim'[Year Rank])-1))
This year sale = Calculate(sum('table'[sale]),filter(all(date_dim),'date_dim'[Year Rank] = max('date_dim'[Year Rank]))

YOY = ([This year sale]/[Last year sale])-1
 
Now use all these measures and year column in table or matrix

[nkaruturi]

This did not work. For some reason "Last year sale" is blank and YOY is resulting same as this year. Below are the formulas I used. I had to tweak YOY formula little bit as currently I am just interested in difference. I imported dates with Start of the year and Year Rank colum

Lastyearsale = Calculate(sum('table'[sales]),filter(all(dateswithrank),'dateswithrank'[Year Rank] = MAX('dateswithrank'[Year Rank])-1))

Thisyearsale = Calculate(sum('table[sales]),filter(all(dateswithrank),'dateswithrank'[Year Rank] = max('dateswithrank'[Year Rank])))

YOY = ([Thisyearsale]-[Lastyearsale])

Sample data for dates in csv format

Year Rank,Year1,CalDate,Month,START of year
1,2001,2001-01-01 00:00:00,January,2001-01-01 00:00:00
1,2001,2001-01-02 00:00:00,January,2001-01-01 00:00:00
1,2001,2001-01-03 00:00:00,January,2001-01-01 00:00:00
1,2001,2001-01-04 00:00:00,January,2001-01-01 00:00:00

2,2002,2002-01-01 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-02 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-03 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-04 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-05 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-06 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-07 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-08 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-09 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-10 00:00:00,January,2002-01-01 00:00:00
2,2002,2002-01-11 00:00:00,January,2002-01-01 00:00:00

[Anonymous]

Have you created a date table?
Lastyearsale = Calculate(sum('table'[sales]),filter(all(datetable),'dateswithrank'[Year Rank] = MAX('dateswithrank'[Year Rank])-1))
You need to call your date table in all

[nkaruturi]

'dateswithrank' is the datetable for me. Not sure if I missing something when you refer 'datatable'.  In my 'dateswithrank' table I have all dates, years, and rank columns.

[latimeria]

hi @nkaruturi ,

'dateswithrank' must be marked as a date table

[nkaruturi]

I dont want to hardcode Years, as I will not  be aware of what years and for how many years of data I will get.

[Anonymous]

Sale_2017 = CALCULATE(SUM('Table'[sale]),'Table'[year]=2017)
Sale_2018 = CALCULATE(SUM('Table'[sale]),'Table'[year]=2018)
YoY = ([Sale_2018] / [Sale_2017])-1

You need these three Measures