# Preventing Year To Date Results From Projecting Forward

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 – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

## Extended Date Table Power Query M Function

In today's blog post, we'll take another look at the Power Query M function for the extended date...

## Rule Of Thirds: The Composition Rules

When it comes to presenting the story, we need to start with the layout. This is where the rule of...

## Evaluation Context In DAX Calculations

In this tutorial, we'll learn about evaluation context in DAX. Evaluation or initial context is the...

## Icons In Power BI | DAX, UNICHAR, UNICODE & Custom Images

Icons in Power BI are used to communicate meaning and add more context. You can use them as an...

## How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique

When working with calculated data, comparing different data sets will sometimes be necessary. I'm going...

## Heat Map – A Great Visualization For Power BI Reports

In this tutorial, youâ€™ll learn how to create a heat map visual using Charticulator. It is used to...

## CALCULATE Function – How It Can Affect Your Calculations On Power BI

In this tutorial, I want to show you what the CALCULATE function can do through a few examples....

## Remove Empty Columns In Power BI

This blog will demonstrate how to automatically remove all empty columns in Power BI through the Power...

## DAX Calculation Groups – Power BI Report for Problem Of The Week #7

I'm going to talk about the solution I came up with for the 7th Problem of the Week. The problem...

## Publishing PowerApps Applications and Changing The Screen Order

In this tutorial, we're going to talk about publishing PowerApps applications and making sure the...

## Power BI Financial Reporting: Allocating Results To Templates At Every Single Row

Here I want to showcase a unique idea around financial reporting, which is allocating results to...

## Optimizing Queries For A Faster DAX Performance

This tutorial will talk about optimizing your queries in DAX Studio. You'll also learn how to mitigate...