How to divide current year value by previous year value

[Anonymous]

Dear power BI community, I would really appreciate If you could help me out with the following issue

I have a table which contains the following variable.

Company       Year    Quarter     value 1  value 2 

Company 1     2010    Q1            123         123

Company 1     2010    Q2            123         123

Company 1     2010    Q3            123         123

Company 1     2010    Q4            123         123

Company 1     2011    Q1            123         123

.

.

so on 

Now I want to create a new column (using custom column) with formula:

                        = current year/ previous year

i.e. for 2011 Q1 it will be:

                        = (2011 Q1 value1)/ (2010 Q1 value1)

Any ideas, how to do this on M language?

Any help will be much appreciated.

Thanks

[artemus · Accepted solution]

1. Create a column with previous year (year - 1)
2. Do a self join (merge) on Year and Quarter with Previous Year and Quarter
3. Expand joined column selecting all "value" like fields
4. Add new columns for each "value" field with value / Joined.value