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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! 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!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors