DAX - Common Date Table

Question 1

What is the risk of not using a common date table?

Answer 1

The different fact tables might represent dates differently.

Making it difficult to compare different dates

Question 2

What is another name for a common date table?

Answer 2

A Date Dimension table

Question 3

What is the common date table necessary for?

Answer 3

Using time intelligence functions

Question 4

What specs must the common date table meet?

Answer 4

1. One record per day
2. NO missing or blank dates
3. Minimum and Maximum dates must cover what you will use in your calculations

Question 5

What are two functions you can use to create a common date table?

Answer 5

1. CALENDAR
2. CALENDARAUTO

Question 6

CALENDAR syntax

Answer 6

CALENDAR(StartDate, EndDate)

Question 7

CALENDAR example

Answer 7

Date=CALENDAR
(
DATE(2017, 1, 1),
DATE(2021, 12, 31)
)

Just writing a long sentence so the formula will left justify

Question 8

What do you need to do after you’ve created a common date table?

Answer 8

Mark as Date table in Power BI

Question 9

What does “mark as date table” do?

Answer 9

Overrides the Power BI’s auto generated date dimension for all time intelligence and date based calculations in DAX within the data model.

Question 10

Where do you go to create a Common Date Table in M?

Answer 10

In the Transform Data area:
Home tab->New Source->Blank Query

Question 11

Create Common Date Table M syntax

Answer 11

=List.Dates(#date(2017, 01, 01), 365*5, #duration(1, 0, 0, 0))

This creates a 5 year date table starting at 1/1/2017

Question 12

What is the #duration syntax?

Answer 12

duration(days, hours, minutes, seconds)

Question 13

What is the List.Dates syntax?

Answer 13

List.Dates(Start as Date, Count as Number, Step as Duration)

Question 14

How do you change the dates list created by going to New Source->Blank Query and using the List.Dates formula to a table?

Answer 14

Tranform Tab->To Table

Question 15

What must you do after creating a common date table using PowerQuery M?

Answer 15

Change the date column you created to have a date format.

Question 16

How can you add additional columns to the date table? For example, year, month, etc.

Answer 16

Transform Data->Add Column->Date->Select you desired period

Question 17

What is the final step after you have created the Date table in PowerQuery M?

Answer 17

1. Close and Apply then Mark as Date Table
2. Establish relationships between new Date table and other necessary tables

Question 18

What does WEEKDAY function give you?

Answer 18

The day of the week as a number 1-7