cancel
Showing results for
Did you mean:
Helper I

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

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

1 ACCEPTED SOLUTION
Helper I
Solved it - this is how it worked out for me

2 PYTD Sales= var _max = date(year(today())-2,month(today()),day(today()))
return
5 REPLIES 5
Helper I

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

Super User

@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

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

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

New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!
Helper I

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!

Helper I
Solved it - this is how it worked out for me

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

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