What are YTD Flags in Power Query?
Dates tables are an integral part of creating data models in Power BI. 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. Creating common DAX calculations such as Year to Date (YTD) and Previous Year to Date (PYTD) can be done using time intelligence DAX functions such as SAMEPERIODLASTYEAR and TOTALYTD, but sometimes these out of the box functions are not sufficient. A very easy and efficient way to calculate these metrics is to create a YTD and PYTD flags in your dates table.
How to Create YTD Flags in Power Query
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. In this blog post I’ll provide step by step instructions on creating these flags using M in Power Query.Â
Step 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.Â
Step 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 screenshot below. We already have our YTD flag created and we have a column for 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 one 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.Â
Step 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.Â
Step 4 (BONUS): Fiscal Years in Power Query
In addition to standard YTD and PYTD flags, you can also easily create fiscal year flags as well. An easy way to creat fiscal year markers is to use logic similar to the screenshot below which uses the Month of Year field to split out fiscal year.Â
You can easily convert this field into a calculation for specific fiscal years.Â
//FY2021 Sales
CALCULATE(
SUM([Sales]),
[Fiscal Year]="FY2021"
)
Conclusion
Creating an thorough dates table is crucial in creating a flexible and effective data model in Power BI. Using YTD flags in your dates table can help make development and maintainence of YTD and PYTD metrics much easier and less complicated. Hopeully this blog has helped show you another way of enhancing your existing dates tables!
Take your Power BI dashboards and visualizations to the next level with help from our team of expert consultants.
FAQs
YTD metrics can be calculated using the native time intelligence DAX functions such as TOTALYTD() and DATESYTD(). You an find more guidance on your specific YTD calculations in our blog post here.Â
You can create a date list using either DAX or M. In Power Query you can create a list using the Number.From()…NumberFrom() syntax. In DAX you can create a new table using the CALENDAR() function. Â
In Power Query, you can calculate the difference between two dates using the Duration.Days() M function.Â
