Forum Discussion

NewbieJono's avatar
NewbieJono
Icon for Post Partisan rankPost Partisan
3 years ago

Colander Table

where is the best place to produce a calander table.   in power query or with dax in the data in model.   does anyone have any code that used to generate these tables? i currently do it manua...
Need Help
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi NewbieJono 

 

I prefer DAX, it is more convinient and flexible. 

try like:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2020,1,1), DATE(2022,12,31)),
"Year", YEAR ( [Date] ),
"Qtr", "Q" & FORMAT ( [Date], "Q" ),
"MonthNO", FORMAT ( [Date], "MM" ),
"Month", FORMAT ( [Date], "MMM" ),
"Day",FORMAT ( [Date], "DD" ),
"YYQ", FORMAT ( [Date], "YY" ) & "Q" & FORMAT ( [Date], "Q" ),
"YY/MM", FORMAT ( [Date], "YY/MM" ),
"Weekday", WEEKDAY ( [Date],2 )
)

 

it is like:

 

  • NewbieJono's avatar
    NewbieJono
    Icon for Post Partisan rankPost Partisan
    3 years ago

    for max date should i use today()

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      hi NewbieJono 

      it depends. TODAY() or MAX(FactTable[Date])