Year-to-Date & Year-over-Year Calculation using DAX in Power BI

Introduction to Power BI and Year-to-Date(YTD) & Year-over-Year(YoY) Calculations

Power BI is a data visualization and business intelligence tool that allows users to connect to different data sources, transform data, and create insightful reports and dashboards. With Power BI, users can easily perform complex calculations such as YTD calculation, which provides a way to view data from the beginning of the year up to a given point in time. YoY growth is a change in a metric compared to the same period one year prior.

There are several approaches to achieve YTD & YoY calculation using DAX in Power BI. Let's use one of the approach to accomplish that.

 

What is Year-to-Date(YTD)?

Imagine you’re in February, looking back at all the data from the beginning of the year (January 1st) until today. That’s YTD. It’s like a running total of your performance throughout the current year.

 

How to Calculate Year-toDate(YTD)?

• Assume we have a calendar & sales table and having a column for sales amount.
• Now use DAX to develop a measure that computes the current year's YTD revenue.

Previous Year-to-Date(PYTD): Now, rewind to the same day in the previous year. The data from January 1st of that year up to that day is PYTD. It’s your benchmark, a reference point to compare your current year’s progress.
 

How to Calculate Previous Year-to-Date(PYTD)?

• Using SAMEPERIODLASTYEAR function we can get the same date of previous year.

Year-over-Year(YoY) Growth: This is where things get exciting! YoY is the change between your current YTD and the PYTD for the same day. It tells you how much you’ve grown (or shrunk) compared to the same period last year.
 

How to calculate YoY growth :

• Subtract PYTD(YTD Rev LY) from YTD Revenue(YTD Rev) :

 

The DAX functions I utilized to get these calculations :

• LASTDATE(Dates) : Returns last non blank date
• STARTOFYEAR(Dates) : Returns the start of year
• SAMEPERIODLASTYEAR(Dates) : Returns a set of dates in the current selection from the previous year
• CALCULATE (Expression,Filter,Filter, …) : Evaluates an expression in a context modified by filters.
• DATESBETWEEN(Dates,StartDate,EndDate) : Returns the dates between two given dates.
   

Conclusion :

Calculating YTD and YOY growth in Power BI using DAX is a valuable technique for analyzing financial performance and identifying trends. Furthermore, it's important to note that this comprehensive approach leverages only pre-defined DAX functions. By understanding and practicing these versatile functions, you can unlock the ability to perform a wide range of complex calculations within Power BI, ultimately transforming your data into actionable insights.