Forum Discussion
year over year variable
Do you create custom column or calculated table?
If if want to show year over year change for past three years, how would this approach change?
It should be column. Please refer to below example.
I took sales sample for few years.
| DateKey | Sales |
| 7/1/2006 | 100 |
| 7/1/2007 | 290 |
| 7/1/2008 | 300 |
| 7/1/2009 | 200 |
| 7/1/2010 | 130 |
| 7/1/2011 | 230 |
| 7/1/2012 | 950 |
| 7/1/2013 | 250 |
| 7/1/2014 | 340 |
| 7/1/2015 | 455 |
Created "LY Sales" column using below formula
LY Sales = CALCULATE(SUM(Sales[Sales]),ALL(Sales),PREVIOUSYEAR(Sales[DateKey]))
Added new column for LY Variance % as below
LY Variance % = CALCULATE(DIVIDE((SUM(Sales[Sales])-SUM(Sales[LY Sales])),SUM(Sales[LY Sales])), ALL(Sales[DateKey]))
Then put the DateKey and LY Variance % on waterfall chart. Here is the result.
Thank you for your help on this, I am almost there I think
Here is my formula
PrioryearSales = CALCULATE(SUM('Sales Data Structure'[AMOUNT]),ALL('Sales Data Structure'[AMOUNT]),PREVIOUSYEAR('Sales Data Structure'[Shipping Date]))
The table looks like this, it is a list of individual invoices that I am summing up using the Amount column
DOCUMENT NO. Z-NUMBER SHIPPING DATE AMOUNT SHIP TO STATE Rep 6618 296 10/8/2013 $ - OK RL 16458-1 410 10/8/2013 $ 35,464.00 WI 16529-2 410 10/8/2013 $ 30,628.00 WI 16626 210 10/9/2013 $ 1,980.00 FL RL 16607 220 10/9/2013 $ 2,240.00 WI JTS 16547 220 10/9/2013 $ 160.00 WI JTS 16596 220 10/9/2013 $ 101.00 WI JTS 16558 229 10/9/2013 $ 810.00 IN 16610 231 10/9/2013 $ 3,570.00 WI JTS 16609 231 10/9/2013 $ 2,460.00 WI JTS In your provided information, your formula should work.
It doesn't error out it just shows up blank