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

AVDB

New Member
Joined
Mar 26, 2013
Messages
2
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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</SPAN>
Values</SPAN>2010</SPAN>2011</SPAN>2012</SPAN>2013</SPAN>Var 13vs12</SPAN>
Sum of Net External Sales</SPAN>80</SPAN>90</SPAN>100</SPAN>110</SPAN>110-100= 10</SPAN>
So these are measures</SPAN>this can not be a measure, since otherwise it will add as a row, underneath my 'Sum of Net External Sales'</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=4><COL></COLGROUP>
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top