Thanks for this great post. But, how can we add date ranges for business use?
Businesses would usually work on a Fiscal calendar, and need to know data within date ranges for Fiscal Year, Quarter, Month, Week.
And not all start on April 4th; e.g. our business fiscal year starts August 1st. How could we add columns to represent these Fiscal types?
Good questions. Personally, I don't use DAX at all for creating date or time tables, instead I use the Power Query functionality in Power BI Desktop. There are some great examples out there of date tables using Power Query (e.g. http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/), which I find easier to extend to different fiscal calendars. In your case you would be shifting the month number by eight, which is a fairly simple Power Query calculation off the month number:
if [MonthOfYear] > 7 then [MonthOfYear] - 7 else [MonthOfYear] + 5
and then the year can be shifted:
if [MonthOfYear] > 7 then [Year] + 1 else [Year]
Also, it might be that you actually need a proper 445 or 454 calendar: (http://www.powerpivotpro.com/2015/03/create-a-445-calender-using-power-query/)
Either way, you should certainly consider Power Query as an option.