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

37 REPLIES 37
Frequent Visitor

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

View solution in original post

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/

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/

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/

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/

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/

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/

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/

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/

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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors