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

How to display sales YTD and sales last YTD over years?

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?Power BI Pic.jpg

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-huizhn-msft
Microsoft

Re: How to display sales YTD and sales last YTD over years?

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.

1.png


Finally, create a Line and Clustered Column Chart, it return the expected result shown in the following screenshot.

2.png


If you have any other issue, please feel free to ask.


Best Regards,
Angelia

View solution in original post

37 REPLIES 37
sqldusty Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

Hello. I think the following post on my blog will be helpful for you. Take a look at the first two DAX calculations: https://sqldusty.com/2015/09/01/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-1/

These calculations were written for SSAS Tabular or PowerPivot but will also work for Power BI. Just ignore the part about setting the Date Table. The syntax should be the same.

yaolin512 Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

I have formuals as following:

Total Sales LY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('DATE'[Date]))

Total Sales = sum('2011-10272016 sales'[Amount])

Total Sales YTD Last Year = CALCULATE([Total Sales YTD],DATESBETWEEN('DATE'[Date],[First Date],[Last Date]))

Total Sales YTD = TOTALYTD([Total Sales],'DATE'[Date])

 

I need to display sales YTD 2015  and sales YTD 2016 at year 2016 in the chart. 

Microsoft v-huizhn-msft
Microsoft

Re: How to display sales YTD and sales last YTD over years?

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.

1.png


Finally, create a Line and Clustered Column Chart, it return the expected result shown in the following screenshot.

2.png


If you have any other issue, please feel free to ask.


Best Regards,
Angelia

View solution in original post

yaolin512 Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

Angelina, 

In your example, I want to have 

 

year 2013: total sales of 2013

year 2014: total sales of 2013 and total sales of 2014

year 2015: sales from 1/1-7/20 in 2014 and sales from 1/1-7/20 in 2015

 

I don't have any problems using my formula to display YTD sales each year. It is just the YTD sales of last year I am struggling. Thanks! 

Baskar Super Contributor
Super Contributor

Re: How to display sales YTD and sales last YTD over years?

Look this blog , i guess it will give u some idea.

 

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

 

 

if not let me know i will help u.

Microsoft v-huizhn-msft
Microsoft

Re: How to display sales YTD and sales last YTD over years?

Hi @yaolin512,

OK, I test the function step by step. For the solution I posted, Date will change with actual date is you use SAMEPERIODLASTYEAR  function combining TOTALYTD function in same CALCULATE(). The measures given will calculate:

year 2013: total sales of 2013 is 17638.
year 2014: total sales of 2013 and total sales of 2014 17638 and 18649.
year 2015: sales from 1/1-7/20 in 2014 and sales from 1/1-7/20 in 2015 11088 and 9573.

Please see the following formula and screenshot.

total sale in 2013 = CALCULATE(SUM(Sales[SALE]),YEAR(Sales[DATE])=2013)

Capture1.PNG

total sale in 2014 = CALCULATE(SUM(Sales[SALE]),YEAR(Sales[DATE])=2014)

Capture2.PNG

total sale during 2015/1/1 to 2015/7/20 = CALCULATE(SUM(Sales[SALE]),AND(Sales[DATE]<=DATE(2015,7,20),Sales[DATE]>=DATE(2015,1,1)))

Capture4.PNG

total sale during 2014/1/1 to 2014/7/31 = CALCULATE(SUM(Sales[SALE]),AND(Sales[DATE]<=DATE(2014,7,31),Sales[DATE]>=DATE(2014,1,1)))

Capture6.PNG

If you calculate the sum from 2014/1/1 to 2014/7/20 using another measure, we we are not able to add multiple measure for same dimension. I will still test and update here.

Best Regards,
Angelia






Please review and test, you will find the function of SAMEPERIODLASTYEAR and TOTALYTD.

Best Regards,
Angelia

yaolin512 Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

Angelia,

 

Somehow, I applied the wrong formula for sales YTD last year yesterday. I tried your formulas again today and worked! Many thanks!!!

 

Thank you everyone else!!! I appreciated it!

duncanwil Frequent Visitor
Frequent Visitor

Re: How to display sales YTD and sales last YTD over years?

Thank you Angelia, that worked perfectly for me!

Anmolgan Member
Member

Re: How to display sales YTD and sales last YTD over years?

How about if I want to view the data of the current month till the starting of the year for all the years, and I have data at the ending of each week which I want to see.

 

For Example:

 

year 2018= want to see data of 1 Jan to 3 March

year 2017= want to see data of 1 Jan to 3 March (Contains data of all the years)

year 2016= want to see data of 1 Jan to 3 March (Contains data of all the years)

year 2015= want to see data of 1 Jan to 3 March (Contains data of all the years)

year 2014= want to see data of 1 Jan to 3 March (Contains data of all the years)

 

How to achive the above.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors