Power BI

Creating a very basic Date table in a Data Model

Every Power BI training in Sydney and Melbourne covers the topic of data modeling to some extent. While the basics of data modeling can be easily understood, some common issues occur just when the concepts of data modeling are put into practice. So we decided to develop an archive to address the common issues occurring while developing a model.

Here’s a link to the archive: http://.powerbitraining.com.au/category/data-modeling-issues-and-solutions/

So far we have covered the first three steps:

  1. Getting Started.
  2. Importing the data.
  3. Data Preparation.

Before we move further, here’s a quick recap of these steps:

Getting Started

For this blog, we will disable the auto detection of relationships by Power BI. This is done to create an environment where you can dive deeply into model creation. Disabling relationship detection is done purely to enhance concept building and is not preferred in ideal conditions.

For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/

Importing Data

The Importing Data phase of a data modeling involved procuring the right tables that can answer the business question at hand.

To Gathered Data for Data Model contains:

  • Product Sheet: It consists of three columns addressing the product details, i.e., Product Code, Product and Product Type. Each Product has a distinct Product Code and falls under only one Product Type.
  • Product Line Prod Type Sheet: Each Product Type is a subcategory of Product Line. This sheet records the subcategory to category mapping.
  • Product List Sheet: Product list contains the details of each Product and the list price associated per unit.
  • Sales Person Per Product Type Sheet.: This sheet contains details of Sales Person associated with the Sales. There exists a many to many relationships between the sales person and the product.
  • Sales Sheet: Sales sheet contains transactional Data for the Sales.

Now you have five queries loaded in your Query Editor. At this stage your data model should contain five un connected tables as shown in the image below.

Data Model – Common Issues And Solution | Power BI Training In Sydney | Power BI Training In Melbourne

For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/

Data Preparation

Data Preparation involves ensuring that the data is capable of delivering value.

Data Preparation involves:

  • Checking the data types and data categories.
  • Making sure that all the required columns are present in data set.

Power BI guesses the data type of the column based on top 1000 Rows.

For a step by step guide on this:  https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne-2/

In this blog post, we’ll cover the topic of creating date table.

Creating Date Table

Date is an important dimension table in any data model. Date Table can be generated in Power BI using either the CALENDER() function or the CALENDERAUTO() function.

Both of these functions generate a single date column table. However, CALENDER(,) requires the starting and ending dates as the arguments to the function. Whereas, CALENDERAUTO() automatically detects the first and last date in the dataset and generates a date table accordingly.

Continue using the Previous file for this exercise

To Create a Date Table:

1.Click on Modeling Tab in the Ribbon.

2.Click on New Table.

Common Modeling issues in Power BI training in Sydney

3. In the Formula bar type the following DAX expression.

Common Modeling issues in Power BI training in Sydney

4.Click on New Column in the Modeling Tab and Type the following expression in the formula bar.

Common Modeling issues in Power BI training in Sydney

5.Click again on New column in the Modeling Tab and type following DAX in the Formula Bar for Month.

Common Modeling issues in Power BI training in Sydney

Now let’s create a column for month name.

6.Click again on New column in the Modeling Tab and type following DAX in the Formula Bar for Month.

Common Modeling issues in Power BI training in Sydney

7.Finally, click on the Date table in the Fields Pan.

8.Click Mark as Date Table.

Common Modeling issues in Power BI training in Sydney

9. Choose date from the Date column drop-down

Common Modeling issues in Power BI training in Sydney

10. Click OK.

Conclusion

Data Modeling is a part of our every Power BI training in Australia. In Power BI Basic course, only the basic concepts related to data modeling are covered whereas Power BI Advanced course is more focused towards data modeling and data transformation. Therefore, understanding what a Data Model is entirely important for both beginners and intermediate level users. This blog is dedicated to understanding the creation of date table in a data model.

Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.

More Checkouts for Power BI Training in Australia.

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *