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

[yaolin512]

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?

[sqldusty]

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]

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. 

[v-huizhn-msft · Accepted solution]

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

[yaolin512]

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]

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.

[v-huizhn-msft]

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)

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

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

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

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]

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]

Thank you Angelia, that worked perfectly for me!

[Anmolgan]

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.