Forum Discussion
Getting variations by chaging data structure
Hi everyone, I have the following dataset: I would like to have a table like this one : For variation between 2018 and 2019 and also variation between Forecast and Sales in each ye...
i would just create a date tabe and then use the year from the hierarchy
see attached
vanessafvg
Community Champion
Community Championwhich part are you struggling with exactly the 2018 / 2019 column or just the variation. Please provide data in text format.
Hi vanessafvg ,
Actually with the 2018 / 2019 columns.
| Date | Product Name | Daily Sales | Daily Forecast | Daily Sales Corrected |
| 01/01/2018 | Product A | 100.991 | 93.000 | 100990,8 |
| 02/01/2018 | Product A | 113.184 | 112.000 | 113184 |
| 03/01/2018 | Product A | 118.390 | 121.000 | 118389,6 |
| 04/01/2018 | Product A | 152.053 | 153.000 | 152053,2 |
| 05/01/2018 | Product A | 164.354 | 156.000 | 164354,4 |
| 06/01/2018 | Product A | 113.929 | 115.000 | 113929,2 |
| 07/01/2018 | Product A | 39.139 | 37.000 | 39139,2 |
- vanessafvg
i would just create a date tabe and then use the year from the hierarchy
see attached
vanessafvg thanks for helping!
Your solution gives the variation between Daily sales and forecast sales but doesn't give the variation between Daily sales in 2019 vs Daily Sales in 2018 or does it ?
Thank you
- vanessafvgno it doesn't
you could do that like this
YOY variance =
var sales = CALCULATE(sum('Table'[Daily Sales]))
var previousyearsales = CALCULATE(sum('Table'[Daily Sales]), SAMEPERIODLASTYEAR('Table'[Date]))
return (DIVIDE(sales,previousyearsales))