cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
user12
Helper I
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
 
Thanks in advance for your support!
1 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 ))

View solution in original post

5 REPLIES 5
user12
Helper I
Helper I

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

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

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!

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

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

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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