How to create a date table if you don't have dates in the dataset?

[Anonymous]

Hello,

I want to create a date table but in my original dataset I don't have a date. I only have weeknumber & year. Do i need to add the dates in the date table? If I do so the weeknumber & year are not unique. I am afraid that this gives errors with the relationships.

Can I create a date table with only weeknumbers as the lowest level?

Thanks!

Maartje

[PaulDBrown · Accepted solution]

@Anonymous 

1) Using a Date Table:
You could create a Date table following the method suggested by @negi007 , though you would need to add a date in your dataset (could for example be the first date for each week). The advantage of this method is that you can use Time Intelligence Functions in your measures.
To create a Date Table, you can use:

Date Table = 
VAR MinYear = MIN('Table'[Year])
VAR MaxYear = MAX('Table'[Year])
RETURN
ADDCOLUMNS(
    CALENDAR(DATE(MinYear, 1, 1), DATE(MaxYear, 12, 31)),
    "Year", YEAR([Date]),
    "WeekNum", WEEKNUM([Date]))

Then create a YearWeek Colum in the Date Table using:

YearWeek = 'Date Table'[Year] * 100 + 'Date Table'[WeekNum]

And you get this table:

Next add the same YearWeek calculated column to your fact table:

And finally, to get a date column in your fact table (in this example, it is the first date of each week), use:

Date =
CALCULATE (
    MIN ( 'Date Table'[Date] ),
    FILTER ( 'Date Table', 'Date Table'[YearWeek] = EARLIER ( 'Table'[YearWeek] ) )
)

You can then create a relationship between the Date fields from both tables:

2) Using a Period Table as a dimension table
However, if the dataset doesn't contain dates, a date table is arguably "overkill", and you can simply use a period table.

For example, take this sample dataset:

Firstly create a new calculated column to get the period (YearWeek):

You can then create a period table using:

And then create a relationship between the corresponding YearWeek Columns:

I've attached the sample PBIX file for your reference

[negi007]

@Anonymous 

you can create a date table like below

Table 2 = CALENDAR(date(2020,1,1),date(2022,1,31))

you need to provide startdate and enddate in the calender function.

[Anonymous]

@negi007 

thanks for your quick response. But I don't have (these) dates in my dataset. The lowest level of dates I have in my dataset is weeknumber. Doesn't it matter that I don't have 2020/01/01, 2020/01/02 etc.? If I create a datetable with these dates in it I don't have unique values in the table. Is that a problem?

Thanks!

[negi007]

@Anonymous This will create unique dates for you. You need to specify the lower and upper limit and for each day there would be a one value in the date table. Since date will be primary table for date it will work for all your date columns.

Table 2 = CALENDAR(date(2020,1,1),date(2022,1,31))