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

1 ACCEPTED SOLUTION
Microsoft

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.

Best Regards,
Angelia

39 REPLIES 39
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.

Frequent Visitor

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

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.

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.

Super User

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

Super User

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
New Member

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.

what can the reason for it?

Super User

Hi,

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

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

Super User

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
Anonymous
Not applicable

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
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 =
--------------------------------------------------------
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]),
FILTER(
DATESYTD(DimTable[Date]),
DimTable[Date] <=
LASTNONBLANK(DimTable[Date],
[Total Bal])),-1,YEAR))```

Thanks.

Super User

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
Anonymous
Not applicable

My Apology Ashish,

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,

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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

Thanks.

Super User

Hi,

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

Hope this helps.

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

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,

Announcements

#### The Power BI Community Show

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

#### Charticulator Design Challenge

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

#### Check it Out!

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