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 year.
I supose I have to do some transformations in dataset, but not getting there!
Can anyone help
i would just create a date tabe and then use the year from the hierarchy
see attached
7 Replies
- which 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 i would just create a date tabe and then use the year from the hierarchy
see attached
Anonymous , Better to have measures likes these using date table and time intelligence
TD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
