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...
