Today, I’m going to go over a solution for a certain issue regarding Year to Date (YTD) calculations. This actually stemmed from a question in the Enterprise DNA Support Forum. I’ll leave a link below to the forum discussion if you’d want to review it. You may watch the full video of this tutorial at the bottom of this blog.
You can view this forum post here – YTD Showing for Months after Last Sale
Year to Date (YTD) refers to the period from the beginning of the current year to a specified date before the year’s end. This is basically based on the number of days from the beginning of the calendar year or even fiscal year up until a specified date. It is commonly used for financial reporting purposes.
In this scenario, we are looking to evaluate YTD sales but we don’t want to evaluate anything beyond the last sale that may have occurred. We only want to show the cumulative YTD total up to the last sale.
This is a unique scenario inside Power BI that you most likely need to solve in some way, shape, or form.
Let’s try to take a look at the main problem.
Main Issue With The Current Year To Date Calculation
As you can see here in our sample Total Sales, it returns blank for results that don’t actually have a result. This is why it only displayed a few days in 2018. You’ll also see here that we only have until the 6th of January.
Now, let’s try to do a simple Year To Date calculation of Sales. Click New Measure under the Modeling tab, and we’ll name the measure as YTD Sales.
Let’s use the TOTALYTD function. This will evaluate the specified expression (in this case, the Total Sales) over the interval which begins on the first day of the year and ends with the last date in the specified date column after applying specified filters (in this case, the Dates).
It’s definitely a very simple and useful function. However, it imposes a certain issue when you implement it like this.
As you can see, it projects that Cumulative Total continuously for every single date of that year. This ultimately results to flatlined numbers.
Now, if I want to make this into a visualization, you’ll see here that there’s nothing and it’s just merely a meaningless visualization.
To make this better, we need to blank out every result, which has exceeded the last sale that we have made.
So the Sales is obviously going to update. However, there might be times when you don’t want to calculate it based on the last sale due to certain reasons.
It might be because you don’t make any sales, or you might have had a public holiday, or something else along those lines. In this case, you probably would want to go to the last purchase date instead of seeing that flatlined number.
Creating The Sales Year To Date Measure
Here’s a technique that you can use to solve that certain issue.
First of all, we’ve got to create a formula for the last sales date and name this measure as Sales YTD.
Now, let’s create a variable and call it LastSalesDate.
Then, we’ll use the MAXX function. MAXX is a great way to find a very specific thing, especially around dates. This returns the largest numeric value that results from evaluating an expression for each row of a table.
After that, we’ll use the ALL function to iterate through every single purchase date.
Then, we’re going to evaluate the actual last purchase date.
Now, if we do not use this, the last purchase date is going to be literally the day on every single one of these days.
So the main reason why you have to use the ALL function here is because this will always find the 6th of January (the last purchase date), and that’s exactly what we want. We want this to be a constant 6th of January.
Then let’s create a variable here and name it as YTDSales.
So, we’re going to write exactly the same formula for the calculation of year to date sales that we have written earlier. Let’s use TOTALYTD to go through the Total Sales and Dates.
Then we will use the RETURN keyword which will handle the defined variables in the previous VAR statements.
Using IF Logic For Calculating The Year To Date Sales
Here, we’re gonna write an IF logic to evaluate whether the MIN DATE is less than or equal to the LastSalesDate. So, that’s less than or equal to the 6th of January in this particular case.
If the result is true, return the YTDSales.
If the statement is false, it should return a blank value.
And that is how we’re going to basically filter out or not show any of the results past the last day that there was an actual sale.
To sum up, all we had to do was somehow integrate the LastSalesDate in an IF statement.
Now, if I actually bring this measure into the Sales table here, you’ll see that the results actually cuts off after the last purchase date and displays these blank values as what we have wanted.
If we look at it without the YTD Sales measure in between, we’ll have a much more truncated table like the one from the image below.
And if we turn this into a visualization, we’ll have it up to the current last purchase date, or in some cases, you can do it up to the last date.
Conclusion
Hopefully, you’ve learned a bit about how you can solve things like this. Remember you need to somehow create some logic that displays a blank in the table because the blank won’t show up in the visualization.
If you don’t implement this technique in your measures at the right time, your visualizations might look a bit odd.
You can also apply this technique in very similar situations where you want to show cumulative totals like YTD, QTD, or MTD to the current date.
Don’t forget to check out Enterprise DNA Online if you are looking to learn more about other unique Power BI topics and techniques that you can utilize in your own models.
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
