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.

[gdrum]

My understanding is that SAMEPERIODLASTYEAR is not supported in direct query mode for PowerBI; I'm in desktop mode so have full powerBI pro, but hence my issue.

This is for dynamics 365 Orders.  I have a booking date field, Business Field (e.g. Biz1) and Total Amount (which is in $'s) for each order. I am merely trying to create charts same as in this thread of YTD 2023 vs. YTD 2022 for Biz1, then from there work towards other date comparison and lastly to compare account of that business bookings.  

I can easily manually create a chart with the filters but I'd like the comparison to be side-by-side and automatic like SAMEPERIODLASTYEAR.  Any help is appreciated. @Ashish_Mathur  @sqldusty or others

[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

[Anonymous]

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.

[Anonymous]

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.

[lovish1sood]

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?

[Ashish_Mathur]

Hi,

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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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?

[Anonymous]

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.