Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Trying to create an easy year over year growth chart but not finding any really simply charts
Would love to show it on a water fall chart
Also not easy to find calculation for year over year variance
Hi @Anonymous Waterfall will be a good choice.
YOY should be simple if you want to use Calendar date 🙂
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
Hi @Anonymous,
Would you please provide more information about "it just shows up blank"?
To calculate difference among each year. You can create a calculated table group by the year and create a calculated column to return the previous year amount for each row, then create another calculated column to return difference. I have created a sample .PBIX file for your reference, you can take a look it to check if it can meet your requirements.
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |