Preventing Year To Date Results From Projecting Forward

No comments

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.

forum post regarding a question on how to prevent year to date results from projecting forward

You can view this forum post hereYTD 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.

sample results for Total Sales calculation

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.

Creating measure for year to date 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).

using the TOTALYTD function for calculating the year to date sales

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.

showing the issue with the defaultyear to date sales calculation

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.

visualized results of the year to date calculation

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.

creating a formula for the last sale date

Now, let’s create a variable and call it LastSalesDate.

creating a variable for the last sale date

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.

using the MAXX function in creating the last sale date measure

After that, we’ll use the ALL function to iterate through every single purchase date.

using the ALL function to iterate through every single purchase date

Then, we’re going to evaluate the actual last purchase date.

evaluating 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.

creating the variable for the year to date sales calculation

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.

using the TOTALYTD function 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 the RETURN keyword for year to date calculation

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.

using IF logic in evaluating the last sale date

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

membership banner 3

***** Related Links*****
Calculate Financial Year To Date (FYTD) Sales In Power BI Using DAX
Showing Month to Date (MTD) To Current Date In Power BI Using DAX
Year To Date Sales For Non Standard Calendar Tables – DAX in Power BI

***** Related Course Modules*****
Budgeting & Forecasting
Mastering DAX Calculations
Time Intelligence Calculations

***** Related Support Forum Posts*****
Dashboard Chart Selection Date To drive Sales YTD And Sales LY YTD
Cumulative Year To Date & Monthly Average Year To Date
Showcase Cumulative YTD And Average Month YTD

For more year to date queries to review see here…..

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.