cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maartjevd
Helper I
Helper I

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

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

1 ACCEPTED SOLUTION
PaulDBrown
Super User II
Super User II

@Maartjevd 

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:
Date Table.JPG

 

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

 

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] ) )
)

 

 

 

Date Table.JPG

 

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

2021-05-17.png

 


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:

table.JPG

 

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

 

You can then create a period table using:
Period Table.JPG

 

And then create a relationship between the corresponding YearWeek Columns:

model.JPG

 

I've attached the sample PBIX file for your reference





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Super User II
Super User II

@Maartjevd 

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:
Date Table.JPG

 

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

 

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] ) )
)

 

 

 

Date Table.JPG

 

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

2021-05-17.png

 


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:

table.JPG

 

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

 

You can then create a period table using:
Period Table.JPG

 

And then create a relationship between the corresponding YearWeek Columns:

model.JPG

 

I've attached the sample PBIX file for your reference





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

negi007
Super User II
Super User II

@Maartjevd 

 

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.
 
negi007_0-1621259260103.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


@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!

@Maartjevd 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))




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors