Compare YTD Value (2021) to YTD of 2020, 2019, 2018

[user12]

Hi,

I would like to compare this years YTD to the YTD of last year and the years before that. Now YTD of last year is fairly straight forward. I solved this with the following function:

PYTD = CALCULATE(TOTALYTD(SUM(Sales[Sales]), SAMEPERIODLASTYEAR(DATESBETWEEN('Dates'[Date],STARTOFYEAR('Dates'[Date].[Date]),TODAY()))))

What I am really struggeling with however is to calculate the YTD from 2 years ago. For analytical reasons however I need this value too. Also I will need to have this value from 3 years ago.

The final result should be a 3 year average from last 3 years of how many of the total years sales on average were completed by todays date in the years before.

I.e.:

2021 --> Target 100 Sales - YTD 30 Sales - 30 % Fulfillment

2020 --> In total 100 Sales - PYTD 40 Sales - 40 % Fulfillment

2019 --> In total 100 Sales - YBYTD 45 Sales - 45 % Fulfillment

2018 --> In total 100 Sales - YBYBYTD 35 Sales - 35 % Fulfillment

--> 3 year average fulfillment by this time of the year was 40 % --> This year we are 10 % behind our timeline

Thanks in advance for your support!

[user12]

FYI my dates tabel has all dates from 2010 until the end of this year

[amitchandak]

@user12 , With help from date tbale

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"))

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

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

2nd Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return

if('Date'[Date]<=_max,CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year))), blank())

2 LYTD QTY forced=
var _max = date(year(today())-2,month(today()),day(today()))
return

if('Date'[Date]<=_max,CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-2,year))), blank())

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[user12]

Hi @amitchandak ,

thanks for the quick reply. Unfortunately this is not working for me. Using the formula "2nd Last YTD Sales" I am getting the total years sales from two years ago and not the YTD from today 2 years ago.

Also the "2 LYTD QTY forced" is not working for me for some reason. The date after the if ( if('Date'[Date]<=...) is not working in the formula.

Do you have any other idea how to solve this problem?

Thanks and best regards!

[user12 · Accepted solution]

Solved it - this is how it worked out for me

2 PYTD Sales= var _max = date(year(today())-2,month(today()),day(today()))

return

calculate(SUM(sales[sales]),datesytd(DATEADD('Date'[Date],-2,YEAR)),filter(ALLSELECTED('Date'),'Date'[Date] <=_max ))

[Mohanad-Mustafa]

This works like magic, thanks a million man, you are a life saver!