Creating a Date Dimension Table in Power BI

 

Problem

How can we create a date dimension table in Power BI? We need data such as month name, year, financial quarter, etc.  Also, how can we access this data directly with DAX?

Solution

A date dimension is an integral part of a data warehouse. A date dimension will have a range of dates with attributes such as Month Name, Year, Financial Quarter, Financial Semester and Financial Year.

In this tip, I will detail a method to create a Date Dimension in Power BI.

Solution Overview

In this approach, I will make use of a Power BI calculated table to generate date values. Also I will be adding attributes such as Month Name, Financial Year, Financial Semester and Financial Quarter with the help of DAX.

Calculated Tables

In a calculated table, the table values are generated by Data Analysis Expression (DAX) and the values are stored in the Power BI model.

DAX Calendar Function

The calendar function returns a table with a single column that contains a continuous set of dates. The start and end date range will be supplied as parameters.

The following formula returns a calculated table with dates between January 1st, 2005 and December 31st, 2015.

=CALENDAR (DATE (2005, 1, 1), DATE (2015, 12, 31))

Creating Date Dimension Calculated table

In the Power BI desktop application, let’s create a new table.

Creating new calculated table - Description: Creating new calculated table

Once the table has been created, now let’s add the DAX expression to generate desirable date values. I will be using the Calendar function to generate date values. In this example, I will be creating date values between 1st January 2015 and 31st December 2020.

=CALENDAR (DATE (2015, 1, 1), DATE (2020, 12, 31))

Use DAX Function to calculate date values - Description: Use DAX Function to calculate date values

This function accepts start and end dates as parameters. I have provided the start and end date as “01/01/2015” and “31/12/2020”. Once updated, the Power BI will generate the date values in the query.

Calculated table with dates between 2015 and 2020 - Description: Calculated table with dates between 2015 and 2020

Adding Attributes to Date Dimension

Let’s add a year attribute to the table and the DAX expression to calculate the year which is “Year([Date])”.

Adding new column for attribute - Description: Adding new column for attribute

Using the Year function to calculate Year - Description: Using the Year function to calculate Year

As above, now let’s add more attributes to the calculated table. I have provided the DAX expression for each attribute below.

Year = YEAR([Date])

Day = Format([Date],"DDDD")

DayofMonth = DAY([Date])

MonthofYear = MONTH([Date])

Month = FORMAT([Date], "MMM") & " " & [Year]

QuarterofYear = ROUNDUP (MONTH([Date])/3,0)

Quarter = "Q" & [QuarterofYear] & " " & [Year]

OrdinalDate = DATEDIFF([Year]&",1,1",[Date],DAY)+1

DayofWeek = WEEKDAY([Date],2)

WeekEnding = [Date] + (7- [DayofWeek])

The fully extended DateDimension table can be found below.

Adding other attributes to the table - Description: Adding other attributes to the table

Summary

The data in the calculated table is self-contained and source independent. Based on the configuration of start and end date, the date range can be extended easily. Hence this approach is ideal for reporting and analysis purposes.

Next Steps
nguồn: https://www.mssqltips.com/sqlservertip/4857/creating-a-date-dimension-table-in-power-bi/

Comments

Post a Comment

Popular posts from this blog

Data Import Best Practices in Power BI

Image
  When you create a data model in Power BI, you should consider how to properly use naming convention and what columns to include, in order to improve usability and performance. This article provides a quick list of best practices valid for both Power BI and Power Pivot. APR 5, 2018 UPDATED Marco Russo DAX     POWER BI     POWER PIVOT     TABULAR    Not all of the suggestions described can be applied to all of your data models. You should adapt these best practices to your specific scenario, looking at how to achieve the goals that are the reason for a certain pattern more than barely apply it without considering the pros and cons of each choice. Even if the article mentions Power BI, all the best practices described are valid for Power Pivot and Analysis Services Tabular models, too. The demo file you can download (at the end of the article) contains a sample Power BI file and the corresponding views defined in a SQL file for ...
Read more

TRIGGER AUDIT TABLE SQL SERVER

 IF OBJECT_ID('GenerateTriggers','P') IS NOT NULL        DROP PROC GenerateTriggers    GO        CREATE PROC GenerateTriggers     @Schemaname Sysname = 'dbo'    ,@Tablename  Sysname    ,@GenerateScriptOnly    bit = 1   ,@ForceDropAuditTable   bit = 0   ,@IgnoreExistingColumnMismatch   bit = 0   ,@DontAuditforUsers NVARCHAR(4000) =  '' ,@DontAuditforColumns NVARCHAR(4000) =  '' AS        SET NOCOUNT ON        /*    Parameters    @Schemaname            - SchemaName to which the table belongs to. Default value 'dbo'.    @Tablename            - TableName for which the procs needs to be generated.    @GenerateScriptOnly - When passed 1 , this will generate the scripts alone..    ...
Read more

Power BI Performance Tips and Techniques

Image
  This post reviews some of the tips and techniques that I covered in my webinar   Performance Techniques for Power BI Data Models . To save blog space I have highlighted the first 5 tips from the presentation. There are more tips and techniques, along with expanded explanations and references in the   presentation slides . The Problem Power BI  is  fast, and the columnar data store is forgiving of large data sets. However, if you find that your Power BI desktop solution is slowing down or that the refresh of a published BI data set takes a very long time review these tips to see if they can help you streamline your data model and reports. My example data for this talk is a customized Tasks table from Salesforce. This real life data table comes from a client and is sanitized for confidentiality. The example has over 382,000 rows which isn’t a large table for Power BI. When the data is loaded into Power BI, the stored file size on disk balloons to over 500MB. In ...
Read more