Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

Comparing year to date values with a measure

Data Bear’s Monday Motivation Blog Post

If you missed last weeks tip on Bin Groups in Power BI please visit the link provided. Today the Data Bear team will unpack a very simple but powerful to compare year to date values with a measure.

Comparing year to date revenue, sales, profit, or any other business metric is invaluable for companies to make quick decisions. This allows businesses to identify why sales might be better or wore than previous years. We will show you today how a simple measure can resolve this.

To compare year to date revenue with a measure we will need a variable that identifies the month of the year. This variable is used in our measure to filter any given value in a certain year to only show the values of the months that has passed year to date.

Comparing year to date values with a measure

Let’s create a report that compares the year to date revenue against previous years by region and product. We simply create a variable that evaluates in which month we are. After that we make use of the calculate function to determine the revenue for the year to date.

YTD Revenue =
var Current_Month = MONTH(today())
return
calculate(Sum(Sales[Revenue]),MONTH(Sales[Order Date])<=Current_Month)
Let’s take a close look at our results. In the below image we have two blocks that displays the difference between graphs. The top block displays the revenue by year and region but it is displayed for the full year for 2018 and 2019 rather than the year to date values for those years that we are comparing 2020 with. In the bottom block we see that applying our measure allows us to compare the values year to date for all of the years 2020,2019 and 2018.
Year to date Values
This is it for our Monday motivation post. We hope that this will add that little extra joy towards your reporting journey.
For more Monday motivation posts go to our blog page.

Leave a Comment

Your email address will not be published. Required fields are marked *