How to create a dynamic Dates Table based on the Source Data of Data Model

Creating a dynamic Calendar Table from the Source Data of Data Model

Scenario:

Suppose, we have a table FactSales, having the Sales details data, for 3 Years, FY14, FY15 and FY16.

Now based on the above source table "FactSales", column "Order_Date" , we can generate the Dates Table using the below methods with DAX Expressions.

1) ADDCOLUMNS Method :

Using the AddColumns Function, along with Calendar Function, with the required Columns definition.
TblDates = ADDCOLUMNS(
  CALENDAR( Min(FactSales[Order_Date]) ,Max(FactSales[Order_Date])),
  "Day", DAY([Date]),
  "WeekDay", FORMAT(WEEKDAY([Date]),"DDD"),
  "MonthNum", MONTH([Date]),
  "MonthName", FORMAT([Date],"MMM"),
  "Quarter", SWITCH(TRUE(), MONTH([Date]) IN {1,2,3} ,"Q1",
MONTH([Date]) IN {4,5,6} ,"Q2",
MONTH([Date]) IN {7,8,9} ,"Q3","Q4"),

  "Year", YEAR([Date]), 
  "FiscalYear", "FY"&RIGHT(YEAR([Date]),2)
  )
Result:

2) A) GENERATE Method - I: 
Using the AddColumns Function, along with Calendar Function, with the required Columns definition.

TblCalendar = GENERATE (
      CALENDAR( Min(FactSales[Order_Date]) ,Max(FactSales[Order_Date])),
      VAR StartOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus 3 would be Tuesday
      VAR CurrentDay = [Date]
      VAR DayNum = DAY( CurrentDay )
      VAR WeekDayNum = WEEKDAY([Date])
      VAR WeekDayName = FORMAT(WEEKDAY([Date]),"DDD")
      VAR MonthNum = MONTH ( CurrentDay )
      VAR MonthName = FORMAT(CurrentDay,"MMM")
      VAR Quarter= SWITCH(TRUE(), MONTH([Date]) IN {1,2,3} ,"Q1",
                                                          MONTH([Date]) IN {4,5,6} ,"Q2",
                                                          MONTH([Date]) IN {7,8,9} ,"Q3","Q4")
      VAR YearNum = YEAR ( CurrentDay )
      VAR FiscalQtr ="FY"&RIGHT(YearNum,2)& Quarter
      VAR CurYear  = YEAR( TODAY() )
      VAR CurMonth = MONTH( TODAY() )
      VAR DayIndex = DATEDIFF( CurrentDay, TODAY(), DAY) * -1
      VAR TodayNum = WEEKDAY( TODAY() )
      VAR WeekIndex = INT( ROUNDDOWN( ( DayIndex + (-1 * IF( TodayNum+
                                            StartOfWeek<= 6 , TodayNum + StartOfWeek, TodayNum +                                                    StartOfWeek-7))) / 7,0))
RETURN ROW (
       "DayNum", DayNum,
        "WeekDayNum",WeekDayNum,
        "WeekDayName",WeekDayName,
        "MonthNum", MonthNum,
        "MonthName", MonthName,
        "Quarter", Quarter,
        "Year", YearNum,
        "FiscalQtr",FiscalQtr,
        "Day Index", DayIndex,
        "Week Index", WeekIndex,
       "Month Index", INT( (CurYear-YearNum) * 12 + MonthNum - CurMonth ),
       "Year Index", INT( CurYear-YearNum )
)
)
Result:

Notes:

In the above Table, the Indexes are calculated based on the Today () date.

2) GENERATE Method - II : 
Ref_Calendar = VAR BaseCalendar=CALENDAR(MIN(tbl_Sales[Order_Date]),
                                                            MAX(tbl_Sales[Order_Date]))
RETURN
GENERATE(
BaseCalendar,
VAR _Date= [Date]
VAR _Year= YEAR(_Date)
VAR MthNum= MONTH(_Date)
VAR Mth_FY= CONCATENATE(CONCATENATE(

                        FORMAT(_Date,"mmm"),"-"),YEAR(_Date))
VAR Qtr= CONCATENATE("Q",ROUNDUP(MONTH(_Date)/3,0))
VAR Qtr_FY= CONCATENATE(CONCATENATE(
                       CONCATENATE("Q",ROUNDUP(MONTH(_Date)/3,0)),"-"),YEAR(_Date))
RETURN ROW(

"Year", _Year,
"MthNum", MthNum,
"Mth_FY", Mth_FY
"Qtr", Qtr
"QTR_FY", Qtr_FY,
"Sort_Id",_Year+MthNum+INT(RIGHT(_Year,2))*12
))

Result :

Notes:

We can generate Month Number from Month Name using below logic in DAX:

MonthNoFromName = MONTH(DATEVALUE(1 &"/"& [MonthName] &"/"& 1))

We can generate Month Name from Month Number using below logic in DAX:

MonthNameFromNo = FORMAT(DATE(1,[MonthNo],1),"MMM")

We can generate Month Number from Month Name using below logic in Power Query:

MonthNoFromName = Date.Month (Date.FromText ("1"& [MonthName]))

The Sort_Id is used sort the Data to display by Mth_FY.

We can generate the Sort Ids for a Month, Quarter and Years, if we have the Year No in the data using the below logic. The Unique number for a Year can be like 1,2,3..in ascending order  :
SortId_Month = (Mth_No)+Qtr_No)+(Year_No*19)
SortId_Qtr= (4*Qtr_No)+(Year_No*19)+1
SortId_Year= (Year_No*19)+18

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------