YTD Flags in Power Query
Dates tables are an integral part of any comprehensive data model. Dates tables allow you to normalize a variety of different date fields and perform calculations and filtering on all of those different fields at the same time. Two of the most common calculations I’ve seen with clients are Year to Date calculations and Previous Year to Date calculations. There are some time intelligence DAX functions as SAMEPERIODLASTYEAR and TOTALYTD that allow for some quick out of the box functions to be performed, but if you’re looking to create a YTD or PYTD flag to help perform your calculations against your date table you’re going to need a work around.
Creating your YTD or PYTD flags as a calculated column might be tempting, but moving those to Power Query can really help both with data model simplicity and performance. Check out this step by step instruction on creating these flags using M in Power Query.
1. Create YTD in M
Luckily for us, M has a native function Date.IsInYearToDate([Date]) that returns a boolean TRUE/FALSE value if that date is equal to or less than the current date. If the date is 12/10/2020 then all dates in your date table from 1/1/2020 to 12/10/2020 will return TRUE, all the others will return FALSE.
I know this is super simple, but your YTD flag is done! Thanks M, great function. Now let’s look at how to create your PYTD flag, it’s a little tougher.
2. Create PYTD Flag in M
I am going to break this calculation out to show the steps involved, although you could most likely create this field in one step. First, let’s create a column that gives us all dates, but moved up one year.
To accomplish this we will use the Date.AddYears([Date],1) function. Let’s name this field Next Year Date.
At this point our data looks like the screen shot below. We already have our YTD flag created and we have a column every date in our [Date] field plus a year. Now for the magic! (I have also created a Today field in the screenshot below, that is for another use case)
Remember the field that we created with all of the dates plus on year? Now we are going to apply the Date.IsInYearToDate() function to that very field. This will give us the dates in our Date field that are essentially YTD for for the previous year, hence PYTD.
This PYTD field will return a TRUE/FALSE for this flag as well. Now that you’ve made both flags, remove the [Next Year Date] column that we created to achieve the desired calculation. This will leave you with the [Date], [YTD Flag], and [PYTD Flag] fields.
3. Use Flags in Calculations
Now that we’ve created our YTD and PYTD flags we can use them in our calculations. In the calculations below I calculate the YTD Sales and PYTD Sales for a fictional use case. These flags are now baked into our data model and available to use with all tables that are connected to your Dates table.
//YTD Sales CALCULATE( SUM([Sales]), [YTD Flag]="TRUE" )
//PYTD Sales CALCULATE( SUM([Sales]), [PYTD Flag]="TRUE" )
It should be noted that by adding 1 year to our Date field we arrived at the PYTD flag. You could go back to PYTD2 and PYTD3 by adding 2 and 3 years to the Date field and following the other steps.
Thanks so much for reading! If you have any comments, suggestions, or feedback make sure to email me at firstname.lastname@example.org.