Luckily, you also have the option of manually configuring the range of dates used by clicking the Update Range button: Obviously, this means that your table could contain a very large date range if, for example, you have a Customer table containing a Date Of Birth column. The table contains a continuous range of dates starting from the beginning of the year of the earliest date found in any column in any table in your Data Model, up to the end of the year containing the latest date found in any column in any table in your Data Model. This table is automatically marked as the Date Table in your model. With this table loaded into the Data Model (and the Order Date column recognised as containing data of the Date data type), when you go to the Design tab in the Power Pivot window you’ll see the new Date Table button enabled:Ĭlicking on the New button will add a new date table to the Data Model, called Calendar: There are a number of ways of building these tables (see, for example, my Power Query query here) but they are all a bit of a hassle – which is why it’s so cool that, in Excel 2016, you can get one built automatically inside the Power Pivot window.Ĭonsider the following table of sales data on an Excel worksheet: As you probably know, whenever you are doing any kind of date or time-based calculations in DAX you should always have a separate Date table in your Power Pivot model.
0 kommentar(er)
