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.

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.

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

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store