# values are in rows by years/ how to make a variance comparison

#### AVDB

##### New Member
Hi
My values are in measures in rows per year. How can I make a column with a variance between 2 years, when I put my years in the column headers?
Any tips ?
thank you

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### Jacob Barnett

##### Board Regular
The beauty of DAX is that it makes no difference in terms of rows and columns, once the formula is written you can use and re-use as desired.

Tip 1

You MUST use a separate date table in your model. This table should have a column of unique, contiguous dates that you will use to relate to your fact table. Your date table can have numerous additional columns such as month, year, financial year, ytd etc. that will help with your analysis. Having this in place also drives many of the DAX time intelligence functions and will enable you to integrate other data sources in your model. You will also be able to integrate totally with your company's financial / reporting calendar.

Make sure the Date column is set as THE date column through Design>Mark as Date Table in the PowerPivot window. (Below I have assumed that you call this table 'dimDate'.)

Tip 2
Try out some of the built-in DAX time intelligence functions. Lets say you have a measure called [Sales] that sums the Sales column in your fact table. A few of ways of working out the % variance could be:

Code:
``````= ( [Sales] / CALCULATE([Sales], DATEADD('dimDate'[Date],-364,DAY) ) -1

= ( [Sales] / CALCULATE([Sales], PARALELLPERIOD('dimDate'[Date],-1,YEAR) ) -1

= ( [Sales] / CALCULATE([Sales], PREVIOUSYEAR('dimDate'[Date]) ) -1``````

You will likely find the last two give the same answer with the first being slightly different - if like me you work in an industry that favours 'symetrical' report periods such as retail or manufacturing you will need to avoid the 365 day years embedded in many of the functions.

You must test these in detail that the embedded logic is doing what you think it is before using!

Tip 3
You could also go a little crazy and try and get into some more advanced DAX. Here is an example of a formula that uses all three of DAX's holy trinity; CALCULATE(), ALL() and FILTER() to open out the filter context and find the previous 'period' to the one you are interested in. The reason to go down this route is that it works with non-standard financial calendars.

Code:
`` = ( [Sales] / CALCULATE([Sales], ALL(dimDate), FILTER( ALL(dimDate), dimDate[Financial Year] = MAX( [Financial Year) - 1 )) ) -1``

For a thorough explanation of this technique have a look at: The Greatest Formula in the World, Part 3 « PowerPivotPro

#### AVDB

##### New Member
Hi Jacob, thanks for the feedback and tips.
I have added a date table. and I can get my columns to show by year. I struggle understanding now, if I need to add this dax formulation in a column in my data table, or as a measure in my pivot. A measure will add it as a row, if I have put my values in the row section of the pivot.
 Fiscal Yr Values 2010 2011 2012 2013 Var 13vs12 Sum of Net External Sales 80 90 100 110 110-100= 10 So these are measures this can not be a measure, since otherwise it will add as a row, underneath my 'Sum of Net External Sales'

<TBODY>
</TBODY><COLGROUP><COL><COL span=4><COL></COLGROUP>

#### Jacob Barnett

##### Board Regular
It needs to be a measure.

Default behaviour would be to add the measure as a column although you would get a comparison column for each year. Part of the beauty of the formulas above is that they are dynamic so as long as the data is there the answer will be correct!

If all you want is a column on the end that shows the 2013 to 2012 variance then you should probably look at 'Sets' which are a Pivot Table function for PowerPivot pivot tables that enable you to produce asymmetrical reporting in a way that normal pivots are not capable of.

Sets can be created through Pivot Table Options>Calculations>Fields, Items & Sets>Create Set Based on Column/Row items. The easiest thing to do is drag in everything you want onto the Pivot and get rid of the unnecessary columns.

Replies
0
Views
219
Replies
1
Views
129
Replies
1
Views
174
Replies
2
Views
165
Replies
3
Views
114

1,195,691
Messages
6,011,166
Members
441,590
Latest member
kukaljcanin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back