Getting variations by chaging data structure

[Anonymous]

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

[amitchandak]

@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.

[Anonymous]

So with my columns that would be :

This Year = CALCULATE(sum('Dataset'[Daily Sales corrected]),filter(ALL('Dataset'[Date]),'Dataset'[Date][Year]=max('Dataset'[Date][Year])))

?

Where is my error? 

Could you please provide the example with the names of my columns? Thank you

[vanessafvg]

which part are you struggling with exactly the 2018 / 2019 column or just the variation. Please provide data in text format.

[Anonymous]

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 · Accepted solution]

i would just create a date tabe and then use the year from the hierarchy

see attached

[Anonymous]

@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

[vanessafvg]

no 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))