Hi all,
Did you know that you can use a Date Table in Power Bi to make your life easier when working with date time periods?
Well watch this video to find out how! Then come back to this post for more info!
Using a date table in Power Bi has a couple of major advantages:
- You can filter across different data sources.
- You can use different date periods.
- Days, weeks or months.
For my date tables, I tend to keep them really basic. I will often use the data I have on hand in my tables, and some easy DAX, to create a really simple table that I can use easily.
Here is a simple piece of code to create a date table:
CALENDAR ( FIRSTDATE ( ‘TrainingLoad’[Session Date] ), LASTDATE ( ‘TrainingLoad’[Session Date] ) )
This will create a simple date table with dates starting at your first session date to your last. This will grow after each session making it really simple to use and maintain. From there you can easily add the week of year numbers using some more DAX. Just right click on your table and add a new column using this code:
WEEKNUM ( ‘DateTable’[Date] )
Keep in mind that this will show the week of the year, not of your season. But you can make a small adjustment by adding minus 5 to the end of the DAX for example, to find the correct first week.
Lastly, you could find the smallest date for the given week to use as a start of the week date. Filter the table to find ALL dates within the week before you use MINX to find the minimum value. I am going to leave this one up to you to figure out, you should have the tools in your toolbox to do this now! But if not, leave a comment below and I will be sure to lend a hand.
Let me know how you are going to use a date table to power your performance through data in the comments below!
As always, hit like on the video and subscribe here for more videos.
Thanks!