cancel
Showing results for
Search instead for
Did you mean:
Advocate III

## Possible Tip/Trick: Dynamic Date Dimension Table

I'm going to share the dynamic date dimension table I built, hopefully it is useful to someone!  I named it DateTable for this tutorial, and my fact table FactTable.

The table is built using the following formula:

DateTable = CALENDAR (MINX('FactTable', [Created Date]), NOW())

Basically, it looks for the oldest date you have for the column [created date], and builds a sequential date range until the time of refresh - so it always has calendar information for all data points in the set.

I added the following columns:

Date = This is built when you create the table

Year = YEAR([Date])

MonthofYear = MONTH([Date])

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

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

DayofWeek = WEEKDAY([Date],2)

ISOWeekofYear = IF ((([OrdinalDate] - [DayofWeek] + 10)/7) >= 53, 1, TRUNC(([OrdinalDate] - [DayofWeek] + 10)/7))

DayofMonth = DAY([Date])

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

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

Week Ending = [Date] + (7- [DayofWeek])

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

With both the numeric and text values, you can use "Sort by" to properly order them in visuals.  If you want your week numbers to start on a Sunday instead of Monday, change the "1" to a "2" in ISOWeekofYear column - however ISO Standard is a Monday Start.

I'm still pretty new to PowerBI, but I'll help if I can!  If you have suggestions or modifications, I'd love to hear them.

EDIT:  I changed how the week number is calculated, so I can use ISO Week Numbers.  This also helped fix the "week Ending column, which broke when spanning multiple years that included a leap year.  Let me know if you find any problems!

EDIT2:  MS just released the timeline slicer for PowerBI.  That probably, mostly makes this table useless.  Read more here: http://blogs.msdn.com/b/powerbi/archive/2015/11/03/visual-awesomeness-unlocked-the-timeline-slicer.a...

39 REPLIES 39
Resolver II

## Re: Possible Tip/Trick: Dynamic Date Dimension Table

Hi

Thanks for this

What would you do if you wanted more than one calendar. Say an Invoice Date plus an Order Date Calendar

Would you need two new tables or could one be set up (called  DateTable) and filter by date type in the measure somehow?

Is this possible

Thanks

EDIT. To do the above

http://community.powerbi.com/t5/Desktop/Linking-2-or-more-dates-to-one-Master-Calendar-USERELATIONSH...

Memorable Member

## Re: Possible Tip/Trick: Dynamic Date Dimension Table

@ALeef Great Job..Only needs a SWITCH for the month names and set...

@RJ You can create a second Calendar or use the same with inactive relantionship & USERELANTIONSHIP.

Konstantinos Ioannou
Advocate III

## Re: Possible Tip/Trick: Dynamic Date Dimension Table

@konstantinos : Why would you use a switch and type in all the names, rather than use the format function?  Is it a perfromance difference?

@RJ :  Check out http://community.powerbi.com/t5/Desktop/Transitive-Relationships/m-p/8563#U8563 .  It's an interesting concept, and apparently having inactive relationships is fine - the software is still smart enough to filter right through.  It wasn't exactly what I ended up using, but that's because I wasn't using a slicer, I wanted a clustered column chart.

Memorable Member

## Re: Possible Tip/Trick: Dynamic Date Dimension Table

@ALeef Apologies..SWITCH is slower of course. To be honest I wasn't aware that FORMAT can return months and days due to always importing Date Table..

Thanks, always learning..

Konstantinos Ioannou
Resolver II

## Re: Possible Tip/Trick: Dynamic Date Dimension Table

BTW I always use today() not now()

Does it make any difference?

Advocate III

## Re: Possible Tip/Trick: Dynamic Date Dimension Table

TODAY() only returns a day, and sets the time to 12:00am.  For this instance, I don't think it matters, but I use timestamps regularly, so that is what I'm in the habit of.

Good question!

Regular Visitor

## Re: Possible Tip/Trick: Dynamic Date Dimension Table

I am new to PowerBI and I don't understand how to actually create this table.

I tried creating a New Source -> Blank Query and inserting your query, but that doesn't seem to work.

Could you please add some more detailed steps on how to add it to the queries, for newbies like me? 🙂

Memorable Member

## Re: Possible Tip/Trick: Dynamic Date Dimension Table

@bernitorres You can create/import tables with 2 ways. One is importing with "Get Data" and the other is through DAX data modelling.

You need to go to the data model view and in the ribbon go to modeling tab & select "New Table". thus using DAX language.

The "Get data" uses M language which is used only for importing data to data model.

Keep in mind that you need to already have import some data in order to see the :Modelling" tab..

Konstantinos Ioannou
Advocate III

## Re: Possible Tip/Trick: Dynamic Date Dimension Table

@konstantinos has it right.  I made it using the New Table option on the ribbon.

## Helpful resources

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors