Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yaolin512
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

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

40 REPLIES 40
Anonymous
Not applicable

Thanks for your time and help Ashish.

The figure which you have shown in the pics are for the month of Jan which is correct figure but only for Jan and with selection....

See the snapshot below, highlighted

 

Capture.JPG

I get the highlighted figures after selecting months from Jan to May... and i need the same result without selecting the Months from Jan to May.... and when the current month is June then the result shoud show from Jan to Jun without any selection...

 

Regards,

Hi,

So you just want to select Year 2019 and June and the 2 measures should add up from:

  1. Jan 2018 to June 2018; and
  2. Jan 2019 to June 2019

Is my understanding correct?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Yes, if current month is Jun...need YTD/LYTD from Jan till date (Current Month)

Hi,

My solution is working fine.  If you select Year 2019 and Month as only 5, the result is correct.  Check again.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Result is correct but it should come without selection, Anyway thanks for your time Ashish...



Hi,

How should the "Month till" be determined?  Should it be till the month of Today?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Yes...i think now it is clear for you

Hi,

Try these measures:

Measure = CALCULATE([Total Bal],DATESBETWEEN(DimTable[Date],DATE(YEAR(TODAY()),1,1),today()))
Measure 2 = CALCULATE([Total Bal],DATESBETWEEN(DimTable[Date],DATE(YEAR(TODAY())-1,1,1),EDATE(today(),-12)))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

 

This solution is perfect for what I'm currently working on! I just need one slight tweak. On your second measure you have listed there, is it possible to bring in Prior YTD Sales, minus one day? ie We report 6/12/19 data on 6/13/19, and when comparing YTD to YTD, the measure given has results that compare YTD 6/13/18 to YTD 6/12/19. I just need to move the parameters for the LYTD measure back one day. I've experimented in the syntax of the measure and can't get that date moved back.

Hi,

Try this measure

Measure 2 = CALCULATE([Total Bal],DATESBETWEEN(DimTable[Date],DATE(YEAR(TODAY())-1,1,1),EDATE(today(),-12)-1))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

WOW, Finally this works... Thanks Ashish...

Measure 2 = CALCULATE([Total Bal],DATESBETWEEN(DimTable[Date],DATE(YEAR(TODAY())-1,1,1),EDATE(today(),-12)))

Once again thanks a lot for your effort and time... ^_^

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks Ashish,

 

I did missed that, now when i make the relation between the two table, i get correct figure but there is one thing that last year figure in the table i don't get. it shows only current year figure... and i believe this is due to the selection we made...

https://drive.google.com/file/d/1T_ZbDf-w8DMkyPMhJuu7IOYmXNMnTPBZ/view?usp=sharing

Can you display the difference of this year ytd and last year ytd .
 
Difference  of 2 YTDS = YTD of this year - YTD of Last Year. 

i am getting error.

please solve this  

Thanks 

sandeep 

Thank you Angelia, that worked perfectly for me!

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! 

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

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.

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!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.