cancel
Showing results for
Did you mean:
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

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

see attached

Proud to be a Super User!

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

Proud to be a Super User!

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.

Tutorial Series Dax Vs SQL Direct Query PBI Tips

Proud to be a Super User!

Regular Visitor

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
Regular Visitor

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
Super User I

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

see attached

Proud to be a Super User!

Regular Visitor

@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

Super User I
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))

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

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