cancel
Showing results for 
Search instead for 
Did you mean: 
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

39 REPLIES 39
sqldusty
Microsoft
Microsoft

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.

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. 

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

Anonymous
Not applicable

Hi Angela,

 

Liked the explanation, its in detail... but what if i do not have date column and i have only month and year coloumn then how do i do this calculation...

 

thanks.

Hi,

If there are 2 columns - one for month and another for Year, then write this calculated column formula

=1*("1/"&[Month]&"/"&[Year])

Hope this helps.


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

Hi Ashish, 

 

Thanks, now i got the date column, can you please help me to calculate LYTD (Last Year to Date) figure...

 

Regards,

Ashik.

Hi,

Create a Calendar Table* and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  In the Calendar Table, extract Year and Month by using the folloiwng calculated column formulas: Year=Year(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm").  Create slicers for Year and Month and select any one Year and Month.  Write this measure

Sales = SUM9Data[Sale])

YTD Sales = CALCULATE([Sales],DATESYTD(Calendar[Date],"31/12")

LYYTD Sales = CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(Calendar[Date]))

* To create a Calendar Table, go to Modelling > New Table and write this formula

Calendar=Calendar(MIN(Data[Date]),MAX(Data[Date]))

Hope this helps.


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

hey! Ashish

I linked my Calendar Date column with the Data date column but still, it's not showing results as if it's not linked. 

 

lovish1sood_0-1616137316563.png

lovish1sood_1-1616137437822.png

 



what can the reason for it?

Hi,

Share some data, explain the business question and show the expected result.


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

Hi Ashish, Thanks for your time,

 

I have follow all the steps to calculate the measures, but here i get same figure for all the measures...

 

Sales = samefigure

YTD = samefigure

LYTD = samefigure

 

I have a question in YTD, why have you mentioned "31/12" what is the use of this.

Hi,

Have you chosen a specific month and Year in the slicer? (The Year and Month slicers should get built from the Calendar Table)  "31/12" implies that the Financial Year ends on December 31.  Share the link from where i can download your PBI file.


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

Please see the PBIX file i have shared in the link...

https://drive.google.com/file/d/1DG1MJ9zEbzFPy7d0C73NSbDW6dwpkbSw/view?usp=sharing

Hi,

You had not created a relationship.  After creating it, the result seems correct.

Untitled.png


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

Hi Ashish,

 

Sharing with you another PBIX file, kindly have a look at it...below are the 4 dax expression used and getting same result and i.e. whole last year, i don't get LYTD.

LYTD2 Bal = 
CALCULATE([Total Bal],DATEADD(DATESYTD(DimTable[Date]),-1,YEAR))
--------------------------------------------------------
LYTD1 Bal = 
VAR DataMaxDate = CALCULATE(MAX(DimTable[Date]),ALL('Fin FactTrialBalanceByDimensionUpToMonth'[Balance]))
return
CALCULATE([YTD Bal],SAMEPERIODLASTYEAR(DATESBETWEEN(DimTable[Date],BLANK(),DataMaxDate)))
-------------------------------------------------------
LYTD Bal = 
TOTALYTD([Total Bal],SAMEPERIODLASTYEAR(DimTable[Date]))
------------------------------------------------------
LYTD3 Bal = 
CALCULATE(SUM('Fin FactTrialBalanceByDimensionUpToMonth'[Balance]),
DATEADD(
    FILTER(
        DATESYTD(DimTable[Date]),
        DimTable[Date] <=
        LASTNONBLANK(DimTable[Date],
        [Total Bal])),-1,YEAR))

https://drive.google.com/file/d/1JRdr2OqvTMWCyHTnvhAqh9TylLGb1KVr/view?usp=sharing

 

Thanks.

 

I do not understand anything there.  You have not even bothered to tell me which figures over there are wrong and what the correct numbers should be.  Very poorly drafted question.  Just uploading a file and expecting me to make sense of your work is a very bad way to ask a question.


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

My Apology Ashish,

Capture.JPG

If you see the above pic, the circled figures for LYTD shows for whole year, its not LYTD.. the figure should be somewhere 39.72...

 

Once again sorry for taking your precious time...

 

Regards,

 

What should be 39.72 - row total/column total/cell value?


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

Again i made mistake, sorry....

Column Total should be 39.72...and i get 88.92 which is for whole year 2018

 

Thanks.

Hi,

The result should be this.  I have verified this from your source data.  Download the PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
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,

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!