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.

Amit Chandak

4 min readJul 15, 2020

Abbreviations:

YTD: Year Till Date
LYTD: Last Year Till Date

Pre-requisites:

For any Time Intelligence, you must have a date table. Please refer the below links to create one date table.

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Make sure your calendar has the following fields.(For a financial year, which is not same as the calendar year, refer the Power BI community blog for some of these calculations.)

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

What are the various functions for YTD in Power BI?

DATESYTD and TOTALYTD
YTD = calculate([Sales],datesytd(‘Date’[Date]))
YTD -1 = TOTALYTD([Sales],’Date’[Date])

How to use these functions if we are not using the regular calendar year?

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

How to get previous YTD/ Last year till date?

LYTD = calculate([Sales],datesytd(DATEADD(‘Date’[Date],-1,YEAR)))
LYTD -1 = TOTALYTD([Sales],DATEADD(‘Date’[Date],-1,YEAR))
LYTD -2 = CALCULATE([YTD],SAMEPERIODLASTYEAR(‘Date’[Date]))
LYTD AM = calculate([Sales],datesytd(DATEADD(‘Date’[Date],-1,YEAR),”3/31"))

What date YTD function will take, if we do not select the date?
End date of the Calendar will be considered as end date of YTD.
How to control YTD and LYTD till today and last year same date ?

YTD Today = calculate([Sales],datesytd(‘Date’[Date]),filter(‘Date’,’Date’[Date] <=TODAY()))
LYTD Today = var _max = date(year(today())-1,month(today()),day(today()))
return 
calculate([Sales],datesytd(DATEADD(‘Date’[Date],-1,YEAR)),filter(ALLSELECTED(‘Date’),’Date’[Date] <=_max ))

How to control YTD and LYTD not to get data after max date of a fact/ table for this year and last year?

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))
return 
calculate([Sales],datesytd(DATEADD(‘Date’[Date],-1,YEAR)) ,’Date’[Date] <=_max )

For previous two questions the solution only applies to current calendar year or financial year.

How to get complete last year data?

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.

How to get this year vs last year without using time intelligence?

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

How to get YTD and LYTD without time intelligence?

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

When we use YTD, we get complete data for last year? How can we restrict it?

YTD Cross Year = 
var _max = datediff(date(year(today()),1,1),TODAY(),day)
return
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)))

Let us know what you think about the above suggested methods.
Share the scenario where it helped you. Please find the file, for you to explore.
Also, would love to hear your approach on the alternate methods of using above Time Intelligence functions.

Want to Learn More, Explore these videos and video series.

Complete Power BI Tutorial/Course in Just 11 Hours

Mastering Microsoft Fabric: 30 Videos

Want to Learn SQL: 20 Videos

Mastering Power BI: 230+ Videos

Power BI Concepts- How Power BI Works- 150+ Videos

Migrating from Tableau to Power BI: 30 Videos

DAX for SQL Users: 10 Videos

What works in Power BI Direct Query- 10+ Videos

सीखे Power BI हिंदी में- 60+ Videos

Power BI Hindi On Demand- 10+ Videos

About the Author: Amit Chandak

Amit Chandak is Chief Analytics Officer at Kanerika Software Private Limited where the priority is to get the most value out of the customers’ data. He is also an avid Power BI Super User due to his contributions to the Power BI community.

Power BI is turning 5! In celebration of this achievement, Amit is sharing 5 blog posts on the community, Series: Power BI turns 5. You can find the links in this post.

If there is any other topic in your mind, do not hesitate to reach out to Amit. He is looking for different talking points to share his thoughts on Power BI.

You can follow his work on LinkedIn, Twitter, Medium, Youtube