Forum Discussion
Advocate IIIcalculate sales difference between two years
Hello Team Awesome,
I have a sales table with a column labelled Total Sales and then I have a separate calendar table with a column for date that has day, month, quarter and year. My historical data goes back to 2018 but I just need to create a measure that can show how much is the sales difference between 2020 and 2021 relative to 2021 and then display it in a visual showing the difference. I.E: I just want to see if my sales for 2021 has gone up or down when compared to 2020.
Please note that I don't have a column that show total sales for 2020 or 2021. When I want to view total sales for a specifc year, I just put the total sales in visual and then filter by date and this way I see the total sales for that specific date.
Any suggestions please?
Thanks
Mohanad
Mohanad-Mustafa , You have few options based on need for this year vs last year based on selected date from date table
example
YTD 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"))Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
2 Replies
- amitchandak
Super User
Mohanad-Mustafa , You have few options based on need for this year vs last year based on selected date from date table
example
YTD 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"))Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Mohanad-MustafaThanks I have used the first example to create Total Sales for 2020 and 2021 and then used a quick measure to subtract both measures and it worked!