Creating a Dynamic Calendar Date Range in Power Query

hey data fans reed here today i'm going to walk you through two ways to create a dynamic date range table using power query creating a dynamic or relative date range for your calendar table can be incredibly useful as it enables you to dynamically load only the dates needed against a fact table or relative date period which will optimize both your model size and increase report performance so let's go ahead and hop into power bi and get started [Music] [Music] so all of the demos for this video are going to be done in power query so we're going to come up to transform data and open the power query editor here we go and the end result of today's video is what you see in front of us here which is where we're going to create the start of a date table that will have a core date column that will have either a relative or dynamic date range for the start and stop dates so let's walk through each one of these pieces one at a time now conceptually there's two ways that i like to retrieve some type of a dynamic date range i either like to grab it for my sales or fact table to get the earliest or latest or i want to specify a relative date period where i want to say go two years backward and two years forward to create a window i'm going to start by discussing the sales date tables first if we click this query here we'll see that this is at 1 1 2020 and then we also have a latest sales date of 12 31 2022 so for either methodology of getting your dates from the fact table or specifying it yourself as a relative date it's generally a recommended practice to start your earliest date at the beginning of the year so january 1st from whatever earliest year you have and the latest year you want to put your calendar table out to in the future you would want that to end on december 31st let's go ahead and walk through the examples of how to retrieve the earliest and latest sales date from a fact table i did this by come up to the sales table and selecting reference and then you can select the order date column come up to transform and if you go over to the date selection here you'll have an option to do earliest or latest so i will do earliest as an example select that now you'll see that it does get the earliest date but it's not at the beginning of the year yet so the final step is coming over here and then going to year and then start of year and this is the exact same process to get the latest one but instead you would grab the latest and then you would do year and end of year and that is how i created my earliest sales date and my latest sales date and what you would also do as well is to make sure this does not load into the model you would turn off enable load which now creates this as a staging query that can be pumped in to the actual calendar table that will get loaded to the model so let's go ahead and delete this and see how we can actually create the calendar table list from this as well come up to home new source and we're going to do a blank query we're going to use the list.dates function and as you can see this returns an option to actually put in a few parameters now for now we are going to put in some fixed dates but we're going to change this to dynamics so let's just start by putting in a couple of dates in here in a date range and as you can see in countdown here it does not give you an option to actually put it a end date it asks for account so that is how many steps forward you want to count by whatever increment you have so in this case i want two years so that will be 720 days and the step i'm going to put in is one which will be stepping one day so one row per day at a time in this table and i'm going to select and vote and you can see that it created a new query down here which invoked a function and we have a couple of components in here if we come up to the top we'll see that the start range right here is the earliest date that i specified the count here and then the duration by which it is counting from so we're going to update these one at a time to point to my earliest and latest sales dates in here let's start by updating this to the earliest sales date query run that you'll see that nothing's changed because the earliest sales date is 1 1 2020. now we can also update this number to be dynamic as well so we're actually going to do a date range difference between the latest and earliest dates to get a number which will give us the count of how much we want to increment it forward so we're going to use a function called number.from and then we're going to subtract the earliest from our latest dates and run that now you might think this would return 720 if it's two years worth of dates between these but actually let's do a quick scroll down and take a look at what our final date is you'll see down here at the bottom it actually is 12 30 20 22 so it actually is inning one day early considering how it is calculating this date range there's actually one day less than a full two years worth of days as a count in increment of days between these two so we're actually going to just modify this a little bit and add a single day to the end of this to give us a full calendar year and there you go now it actually ends at the end of the year at 12 31 so perfect we have now taken this query and made it relative we've started it on the period over here that will change with our data and this number increment here in the middle will also change as the data in our fact table changes now if i try to delete the original query over here it's not going to let me because i don't actually need this anymore so it will still say though that it's been referenced so that right here query 1 is being referenced at the beginning here so we're going to modify this a little bit more i'm going to come up to query one select it replace it with list.dates there we are now i can delete this query over here select it delete query one and now i can finish this up i'm going to take this and convert it to a table need to change any of these settings select okay there we go rename this column to date and change the data type and now we have the base for our calendar table that will have that relative date range so we've now seen how to create a list of dates based off of a relative date period from the sales table which will give us the exact years full years that we'll need based on our fact table however let's also discuss the relative dates as well so my relative date start if we look in here is finalized to a result of 1 1 2019 but if you look at the source here i want to walk you through this what i'm actually doing here is i'll step through piece by piece i'm actually taking whatever the day is today which is retrieved by the function datetime.localnow and now i'm also adding years to it using the function date dot at years i'm adding minus two years to that so that is taking whatever the current day is today going two years backwards in time and then i'm also wrapping that finally in one final function the start of the year which then anchors it to the beginning so i'm basically saying from whatever the day is upon refresh always go back two years and my relative date end same thing i am going forward in here two years so i've created a four-year relative date window from today's date always making sure to go to the beginning of that year or to the end of that year and if i wanted to use this methodology instead of the fact table same kind of process where for my calendar table that list dates function that i have in here instead of the two queries that i had for my fact table i would replace it with my relative date start and relative date end in these sections here for the start date and then to grab that number as well to know the increment of how far forward do i want to march that date and some of the biggest benefits of creating these dynamic or relative ranges is that you've limited the rows in your calendar table which will reduce model size and also make the performance for time intelligence functions and other stuff a lot faster i've seen a lot of people who have slow reports where the slicers and stuff takes a long time to calculate year over year etc and they've had 50 years of data in their calendar table so this keeps it nice and compact and clean and i will also have a part two that's coming out for this where i'll show you how to add dependent and independent date hierarchy columns onto your calendar table so check out that next week please don't forget to like comment or share this video now this is your first time to my channel or you want to see more of these awesome videos please click that subscribe and notification button also feel free to show your support by becoming a channel member last but not least you can download the file for today's video from my blog files page using the link down below so until next time

Channel: Havens Consulting

Views: 23,562

Keywords: Power BI, PowerBI, PBI, DAX, Data Modeling, Visualizations, Tips & Tricks, PowerPivot, Power Pivot, Power Platform, Power Query, Power BI for Beginners, Power BI Training, Power BI Desktop, Power BI Best Practices, Power BI Relationships, Power BI Dashboard, Power BI Tutorial, Power BI DAX, Power Query Excel, Power BI Versus Excel, Power BI Vs Excel, Power Query Tutorial, Power Query Functions, Power Query Parameters, Power Query Editor, Drill Through, Power BI Service

Id: lDjKS3Ur3ks

Length: 7min 35sec (455 seconds)

Published: Tue Sep 28 2021