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
Super User

@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
Super User

@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
Super User

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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