Variation between the last 2 years of a chart

[PwrBI01]

Hi guys,

I am trying to calculate the porcentual variation between the last 2 years in a table. 

To achieve this, I tried to calculate the YTD Sales for the last year (it is called CurrentYTDSales) and the YTD Sales for the previous year (it is called PreviousYTDSales), but PreviousYTDSales is not working correctly, so I can't calculate the difference.

Variation between Total YTD last 2 years = 
Var EndDateCurrentYear = EOMONTH(MAX('Calendar table'[Date]);0)
Var StartDateCurrentYear = DATE(YEAR(EndDateCurrentYear);1;1)
Var EndDatePreviousYear = EOMONTH(MAX('Calendar table'[Date]);-12)
Var StartDatePreviousYear = DATE(YEAR(EndDatePreviousYear);1;1)
Var MeasureYTDSales = TOTALYTD(SUM(Sales[Sales]);'Calendar table'[Date])
Var CurrentYTDSales = CALCULATE(TOTALYTD(MeasureYTDSales;'Calendar table'[Date]);FILTER(Sales;Sales[Date]>=StartDateCurrentYear && Sales[Date]<=EndDateCurrentYear))
Var PreviousYTDSales = CALCULATE(TOTALYTD(MeasureYTDSales;'Calendar table'[Date]);FILTER(Sales;Sales[Date]>=StartDatePreviousYear && Sales[Date]<=EndDatePreviousYear))
Var VariationLast2Years = ROUND((CurrentYTDSales-PreviousYTDSales)/PreviousYTDSales*100;0)
Return
VariationLast2Years

Is there a way to do it?

The file is in the following link: https://www.dropbox.com/s/jeedthxl5i0nhss/Sales%20data.pbix?dl=0

Regards.

[V-pazhen-msft]

@PwrBI01 

Instead of dateadd you may just use SAMEPERIODLASTYEAR().

Lets say if you already have a YTD sales measure.

Last YTD sales = Calculate([YTD sales], SAMEPERIODLASTYEAR(Calendar[Date]))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[PwrBI01]

Hi @V-pazhen-msft, thank you for your answer but that way doesn't work neither.

I will try to explain the problem in another way to make it easier to understand.

1. I have created the following measure:

'YTD Sales' = TOTALYTD(SUM(Sales[Sales]);'Calendar table'[Date])

2. I have introduced 'YTD Sales' and Calendar table [Year]' measures in a chart, getting the chart on the left of the photo:

3. After that, I introduced a filter to show the last 2 years of 'Calendar table [Year]' (I mean the maximum year of 'Calendar table [Year]' and the previous one). They are on the right part of the photo.

4. Now I want to get the variation between the last year and the previous one as:

Variation between the last year and the previous one = ('YTD Sales current year'-'YTD Sales previous year')/'YTD Sales previous year'

Thanks in advance.

Regards.

[amitchandak]

@PwrBI01 , if YTD is based on today not a date or selected date try like example

YTD=
var _min = date(year(today()),1,1)
var _day = today()
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _max))

Based on date slicer

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Last to Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

[PwrBI01]

Hi @amitchandak,

Thank you for your answer.

The dates are based on a data slicer, but they have just a filter by month.

When I tried your measure, the YTD Sales works correctly, but I have a problem with 'Last YTD Sales'. I get the error message 'Couldn't load the data for this visual. MdxScript(Model) (7, 57) Calculation error in measure 'Sales'[Variation between Total YTD last 2 years]: Function 'DATEADD' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship.'

Regards.