How to get the most out of date automation in Power BI.
Power BI has great date functionality out the box but as you grow your model you will at some point benefit from having a Date / Calendar Table. It usually happens when you need to create custom date fields like Financial Year | Financial Month also referred to as Fiscal Year | Fiscal Month. There are other reasons to have a date table but that is a topic completely on its own.
Below are a few tips on how to get the most out of your Date/ Calendar Table.
Tip #1 - Identify and tag your Date Table
Power BI let's you identify and tag your table as a Date table so do that straight away. Here is a 35 second video showing you how...
Tip #2 - Prevent Year and Month etc. to be summed in error
Year and Month are often configured as Whole numbers so that you can do time based calculations easily. Unfortunately when you click on it so that it is added to a visual the default will be that it is seen as a number and is summed/ aggregated.
Before
After
Here is a 28 second video showing you how to change this ...
Tip #3 - Pre-build key columns/ fields like Start of Quarter, Fiscal Year etc.
Building out new calculations / slicers/ filters are so much quicker when you already have what you need, so keep adding repetitive date based columns/ fields to the Date/ Calendar table. Here is a link to a Table that is a great start. Just copy and paste the contents into your Just copy and paste the text into the Advanced Editor of a blank Query.
Easy to do, easy not to do, so just do it :)
Incremental improvements to your model will pay dividends over time. Here's to having more efficient models.