Forum Discussion
year over year variable
Hi Anonymous Waterfall will be a good choice.
YOY should be simple if you want to use Calendar date :)
- Calculate Last Year Values using SAMEPERIODLASTYEAR
- Calcuate Variance based on TY-LY
- Calcuate Varinace % based on Variance/LY
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.