How to Create a DATE TABLE in Power BI // DAX, From Source, Power Query // Step by Step Guide

in this video i'm gonna go through the three different ways you can create a date table in power bi and why you even want to make them in the first place and why they're so important especially when you start working with bigger more complex data models in power bi all of that and more so without further ado let's get started [Music] hi my name is fernan and welcome to the solutions abroad youtube channel where i focus on teaching beginners the wonderful world that is power bi i upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out when working with data models that require some sort of time intelligence functions your model must have at least one date table it means a table that has at least one column with a unique date on each row now if you're working with power bi for the first time and you bring in some data you'll notice that you'll be able to do some time intelligent functions straight away and that's because by default power bi has the auto date time feature enabled this feature enables report authors to quickly visualize their data without having to learn how the modeling works under the hood but you'll notice that as soon as you work with larger data models so more tables or more date fields you'll start to hit some of the limitations with this feature one of them is the fact that your model doesn't follow the single date table design which means that if you want to create a filter that spans across multiple tables it usually won't work as well as you think it will because you have multiple date tables let me show you what i mean i have a power bi report here with a simple data model two tables employees and terminations our first table has the employee's ids name and their start date on the termination table we have their ids and their termination dates i've also created some sample line charts to visualize both highs and terminations in a time series now imagine you're not able to change the data sets and you were asked to combine the line charts into one so we create a line chart in the axis we put the start date and in the value we put the employee id so far so good right now we need to count the termination in this chart somehow so we drag the employee id from the termination table into the value field you'll notice that the line chart cannot count the employee id from the termination table because it doesn't understand its date context to the employee table this problem happens because you essentially have two date tables which works in the context to itself but it becomes a problem when you want your time intelligence in relation to other tables this problem is what the date table solves it acts as the central date table for your data model so you can propagate and make sense of your filters from different tables before we start creating a date table let's disable the auto date time feature first this feature creates a hidden calendar table underneath each date type field you have in your model which is why you're able to do time intelligence straight away but as you can imagine if you have bigger models with more date fields this can bloat your data size which is another problem that autodate time feature has so head over to file options and under time intelligence you'll make sure that this box is unticked and under data load in current file disable auto time when you hit ok pay attention to the date fields you have under fields you'll notice that the calendar icon next to them disappears this means that these fields don't have a hidden calendar beneath them anymore and we're ready to create our date table click the data view and click new table we're going to create a date table using dax which is one of the ways you can create them in power bi let's name our table date equals then we'll type calendar auto it asks as a variable but let's leave that empty for now let's close the function and hit enter what you see is it then generates a date column for the whole year one row for each day the calendar auto function generates a range of dates based on all the date tables you have in your module and since our dates on both start and termination dates are in october it generates us just this fiscal year now we'll need to mark this as a date table so under tools click mark as date table select the column we made dates and once you hit ok pay attention to the date field under the fields tab that icon there means that this field is now marked as a date table let's now create a relationship between our dates from the two tables we have so move to the relationships window drag start to date drag termination date to date let's go back to the report view and let's finally fix our line chart under axis let's remove start date and replace this with our date column that we created and there you have it now that these two tables share the same date table you're able to combine them into the same axis now that you understand how to use a date table and why you need to use them let's go through some of the different ways that you can create date tables in power bi there are three different ways that you can create date tables one is what i showed you just now by generating it through dax the second way is to connect to it directly from your source so if you're connecting from a data warehouse or a database chances are it already has a date table in them so use it you can connect to it and get it the same way that you would get your other tables it's actually recommended for you to get your date tables from the source just because it removes the extra work for power bi to generate it and also it gives your organization a single source of truth for these day tables the other way that you could create a date table is through power query now i learned this method from resarad from radacad.com i'll leave a link to his blog in the description box below if you want to check it out what you want to do is hit transform data to open power query under new source click blank query then you want to hit advanced editor clear the text and you want to paste this short m code and hit ok i'll link to it in the description box below if you want to copy the code immediately you will have a date field with dates in it spanning from the year 2000 to 2020. you can change the start and end date from the m code itself it's not too complicated on lines two and three if you click close and apply you can now use this table as a date table same way as we did it with the dax date table so mark it as date table then create the relationships and that's all i have for you regarding date tables in power bi i hope i've convinced you to start using date tables in your data models from now on so what did you think do you think you'll be able to create date tables on your own now let me know in the comment section below give this video a like if it helped you it's the best way to let me know that you enjoy this type of content get in touch using the social media links that i included in the description box below and thank you so much for watching guys see you again on the next one

Channel: Solutions Abroad

Views: 19,754

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, power bi 2020, data analytics, dax, data modelling, data visualisation, business intelligence, date tables, power bi date table, power bi create a date table, power bi dax date table, auto datetime, power bi dates table, power bi dates, mark as date table power bi, calendarauto, dax calendarauto, m code generate date, data source date, date table, why date table

Id: 5i41-FiscYI

Length: 7min 28sec (448 seconds)

Published: Wed Nov 11 2020