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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

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 June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors