Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.