Forum Discussion
How to divide current year value by previous year value
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
- Create a column with previous year (year - 1)
- Do a self join (merge) on Year and Quarter with Previous Year and Quarter
- Expand joined column selecting all "value" like fields
- Add new columns for each "value" field with value / Joined.value
1 Reply
- artemusMicrosoft Employee
- Create a column with previous year (year - 1)
- Do a self join (merge) on Year and Quarter with Previous Year and Quarter
- Expand joined column selecting all "value" like fields
- Add new columns for each "value" field with value / Joined.value