Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
i would just create a date tabe and then use the year from the hierarchy
see attached
Proud to be a Super User!
@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.
So with my columns that would be :
Proud to be a Super User!
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
Proud to be a Super User!
@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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |