In today’s blog post, I’d like to discuss what we like to call the period table. The period table creates a distinct date range so you can slice your data any way you want. I’ll show you how you can create a dynamic date slicer in Power BI by using this period table. You may watch the full video of this tutorial at the bottom of this blog.
As you can see, I have All Dates, Yesterday, Today, 5 Days, 15 Days, 1 Month, 3 Months, 6 Months, Year to Date, 1 Year, and 2 Years of data.
I’ll show you how I came up with the idea for this. Let’s hop on over to the Enterprise DNA forum and I’ll show you where we got this post.
This is a post that I posted in the Enterprise DNA forum back in May of 2020.
I originally got this idea from a blog post that Chris Webb did a couple of years back. You can click on the forum link above to read my thread and see how I came up with it. You’ll also see the M code that I created to implement this strategy.
I come up with this tutorial because another thread recently popped up in the Enterprise DNA forum. A member with a Yahoo Finance idea was trying to get API connected to Power BI. You can see from this link all of the things that we discussed on this problem.
The Enterprise DNA forum is a great way to learn and bring your Power BI development to the next level.
Brian Julius, another Enterprise DNA expert, and myself were tasked with slicing the forum member’s data during these time periods. Brian helped with connecting the data to the API so that the member could bring in all the information from Yahoo Finance.
Let’s go back to the Power BI desktop and take a look at the period table. This is a simple table with three different fields that are included in this period.
Let me show you how I did this. First, we’ll go to the Home tab and click on Transform data.
All of this was created in the query editor with M code. This is how the data model is set up.
One of the first things that I did was to create a query for the MinDates.
I went to the advanced editor and found the minimum date that was in the Dates table in this example.
And then I created MaxDates using an M code to get the maximum date in the Date table. I know there are other ways of doing this but this is how I set it up for this example.
Now, let’s open up the period table.
This will give us all of the examples of those dates ranges that were available on the previous page that I mentioned earlier.
If we open up the advanced editor, you’ll see the M code that was basically just copied and pasted from the first forum post that I mentioned.
I wanted to get Today’s Date first so you’ll see that referenced throughout the code. To get All Dates, I used the MinDates and MaxDates that we created earlier before we opened up the period table.
Once I got the example for 5 Days, it was very easy to do the rest of the days.
For the months, I changed the M code from Date.AddDays to Date.AddMonths to get the difference in months.
And then YTD is basically Date.StartofYear which is in TodaysDate. I also added the one year and two years of data from today.
I just want to re-emphasize that if you go to that post in the Enterprise DNA forum, you’ll be able to easily access this M code.
Let’s close the power query editor and go back to the model. On this set up, we can see up to 6 months worth of data.
When we click on YTD, we can see the year-to-date data.
Then when we click on 1Y, we can see the data for the full year.
***** Related Links *****
Compare Data Points From Different Timeframes In One Visualization – Power BI
Show Cumulative Totals Unaffected By Date Slicer Selection In Power BI
Making A Metric Selection Slicer In Power BI
Conclusion
Using this M Code from the forum will allow you to create a dynamic date slicer in Power BI. With this technique, you can easily slice the data according to different time periods.
I hope you enjoyed this tip, and if you did, please watch the accompanying YouTube video and make sure to click on the like and subscribe button. You don’t want to miss out on any upcoming content on the Enterprise DNA YouTube channel.
[youtube https://www.youtube.com/watch?v=jKBfMvhU9R0?rel=0&w=784&h=441]
