I created a Line and Clustered Column Chart to display sales from 2011 to 2016. I want to display ALL sales of current year and sales of previous year from 2011 to 2015. However, I don't have intact sales of 2016. Therefore, I want to compare sales YTD of 2016 and sales YTD of 2015 comparing 2016 in the same chart. It displays sale of 2015 and sales YTD of 2016 now. I contacted technical support. He told me that I can use forecasting. I don't think forecasting would help. Anyone can help?
Hi @yaolin512,
For your requirement, you’d better use SAMEPERIODLASTYEAR and TOTALYTD function. SAMEPERIODLASTYEAR Function Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, it will change with actual date is you use it combine TOTALYTD function. I try to reproduce and get the expected result as follows.
My sample sale data is from 2013/1/1 to 2015/7/20. I create measure use the following formulas.
Total Sales = SUM(Sales[SALE])
Total Sales YTD = TOTALYTD(Sales[Total Sales],'Sales'[DATE])
Total Sales YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR('date'[date]))Then create a table used to display the results. Please not the highlighted in yellow background, Total Sales YTD will calculated the sum of sales during 2015/1/1 to 2015/7/20 in 2015 year. While the sales are full in 2013 and 2014, the Total Sales YTD Last Year measure calculates the sum of sales in whole last year.
Finally, create a Line and Clustered Column Chart, it return the expected result shown in the following screenshot.
If you have any other issue, please feel free to ask.
Best Regards,
Angelia
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
