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

[khanashique]

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.

[Ashish_Mathur]

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.

[khanashique]

Hi Ashish, 

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

Regards,

Ashik.

[Ashish_Mathur]

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.

[khanashique]

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.

[Ashish_Mathur]

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.

[khanashique]

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

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

[Ashish_Mathur]

Hi,

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

[khanashique]

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.

[Ashish_Mathur]

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.

[khanashique]

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,

[Ashish_Mathur]

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

[khanashique]

Again i made mistake, sorry....

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

Thanks.

[Ashish_Mathur]

Hi,

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

Hope this helps.

[khanashique]

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,

[Ashish_Mathur]

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?

[khanashique]

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