cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Getting variations by chaging data structure

Hi everyone, 

 

I have the following dataset:

mafaldavs123_0-1603727271662.png

I would like to have a table like this one :

mafaldavs123_1-1603727306557.png

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

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

see attached

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Super User I
Super User I

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @vanessafvg ,

 

Actually with the 2018 / 2019 columns.

 

DateProduct NameDaily SalesDaily ForecastDaily Sales Corrected
01/01/2018Product A100.99193.000100990,8
02/01/2018Product A113.184112.000113184
03/01/2018Product A118.390121.000118389,6
04/01/2018Product A152.053153.000152053,2
05/01/2018Product A164.354156.000164354,4
06/01/2018Product A113.929115.000113929,2
07/01/2018Product A39.13937.00039139,2

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

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

 

see attached

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@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

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors