Power BI — Year on Year with

In this blog, we will try to answer, 10 Most Frequently Asked Questions on Year Till Date (YTD) in Power BI.



Date = CALENDAR(date(2015,03,01),Date(2021,02,28)) //calendar
Start Of Year = STARTOFYEAR(‘Date’[Date],”12/31")
End of Year = ENDOFYEAR(‘Date’[Date],”12/31")
Day of Year = Datediff([Start Of Year],[Date],Day)+1
Year = YEAR(‘Date’[Date])
YTD = calculate([Sales],datesytd(‘Date’[Date]))
YTD -1 = TOTALYTD([Sales],’Date’[Date])
You can give end date in both DATESYTD and TOTALYD
YTD AM = calculate([Sales],datesytd(‘Date’[Date],”3/31"))
YTD AM -1 = TOTALYTD([Sales],’Date’[Date],”3/31")
This means YTD will start from 1st April
LYTD = calculate([Sales],datesytd(DATEADD(‘Date’[Date],-1,YEAR)))
LYTD -1 = TOTALYTD([Sales],DATEADD(‘Date’[Date],-1,YEAR))
LYTD AM = calculate([Sales],datesytd(DATEADD(‘Date’[Date],-1,YEAR),”3/31"))
YTD Today = calculate([Sales],datesytd(‘Date’[Date]),filter(‘Date’,’Date’[Date] <=TODAY()))
LYTD Today = var _max = date(year(today())-1,month(today()),day(today()))
calculate([Sales],datesytd(DATEADD(‘Date’[Date],-1,YEAR)),filter(ALLSELECTED(‘Date’),’Date’[Date] <=_max ))
YTD Max Date = var _max = maxx(ALLSELECTED(Sales),Sales[Sales Date]) 
return calculate([Sales],datesytd(‘Date’[Date]) ,filter(‘Date’,’Date’[Date] <=_max ))
LYTD Max Date = var _max1 = maxx(ALLSELECTED(Sales),Sales[Sales Date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
calculate([Sales],datesytd(DATEADD(‘Date’[Date],-1,YEAR)) ,’Date’[Date] <=_max )
LYTD Complete = calculate([Sales],datesytd(ENDOFYEAR(DATEADD(‘Date’[Date],-1,YEAR))))
Previous Year = CALCULATE([Sales],PREVIOUSYEAR(‘Date’[Date]))
These will complete last Year as Grand total, even if you use month year. So better only use with Year as group by or no Time group by.
(Need to use row context)
This Year = CALCULATE([Sales], FILTER(ALLSELECTED(‘Date’),’Date’[Year]=max(‘Date’[Year])))
Last Year = CALCULATE([Sales], FILTER(ALLSELECTED(‘Date’),’Date’[Year]=max(‘Date’[Year])-1))
This Year YTD = CALCULATE([Sales], FILTER(ALLSELECTED(‘Date’),’Date’[Year]=max(‘Date’[Year]) && ‘Date’[Day of Year]<=MAX(‘Date’[Day of Year])) )
Last Year YTD = CALCULATE([Sales], FILTER(ALLSELECTED(‘Date’),’Date’[Year]=max(‘Date’[Year]) -1 && ‘Date’[Day of Year]<=MAX(‘Date’[Day of Year])) )
YTD Cross Year = 
var _max = datediff(date(year(today()),1,1),TODAY(),day)
calculate([Sales],datesytd(‘Date’[Date]),’Date’[Day of Year]<=_max)

Few other important measures

Trailing Year = CALCULATE([Sales],DATEADD(‘Date’[Date],-1,year))
Trailing 2 Years = CALCULATE([Sales],DATEADD(‘Date’[Date],-2,year))
Trailing Week Year = CALCULATE([Sales],DATEADD(‘Date’[Date],-364 ,Day))
Last to Last YTD = calculate([Sales],datesytd(DATEADD(‘Date’[Date],-2,YEAR)))

About the Author: Amit Chandak

Amit has 18+ years of experience in Business Intelligence, Analytics, Data Science. He is Chief Analytics Officer at Kanerika & Power BI community Super User