Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Insert Unknown Date into Calendar Table

Hi,   I need to create a calendar table that has a row for the unknown dates. It needs to look like this:   I was able to create a calendar table with the code below, but I can not find ...
General Comment
Interesting Links
Tips and Tricks
Tutorial Requests
  • CrisYan's avatar
    CrisYan
    8 years ago

    You could do something like

    Calendar = 
VAR C1 = ADDCOLUMNS (
CALENDAR (DATE(YEAR(NOW())-1;1;1); DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY())));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

VAR C2 = 
ROW(
"Date";0;
"DateAsInteger"; 0;
"Year"; 0;
"Monthnumber"; 0;
"YearMonthnumber"; 0;
"YearMonthShort"; 0;
"MonthNameShort"; 0;
"MonthNameLong";0;
"DayOfWeekNumber"; 0;
"DayOfWeek";0;
"DayOfWeekShort"; 0;
"Quarter"; 0;
"YearQuarter"; 0
)

RETURN UNION (C1;C2)

    Replace the zeros in C2 with the desirable values of that row, and be aware with the Data type of each column.

     

    Regards!

CrisYan's avatar
CrisYan
Resolver III
8 years ago

You could do something like

Calendar = 
VAR C1 = ADDCOLUMNS (
CALENDAR (DATE(YEAR(NOW())-1;1;1); DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY())));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

VAR C2 = 
ROW(
"Date";0;
"DateAsInteger"; 0;
"Year"; 0;
"Monthnumber"; 0;
"YearMonthnumber"; 0;
"YearMonthShort"; 0;
"MonthNameShort"; 0;
"MonthNameLong";0;
"DayOfWeekNumber"; 0;
"DayOfWeek";0;
"DayOfWeekShort"; 0;
"Quarter"; 0;
"YearQuarter"; 0
)

RETURN UNION (C1;C2)

Replace the zeros in C2 with the desirable values of that row, and be aware with the Data type of each column.

 

Regards!

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    It worked exactly as I wanted it, thanks!

     

    This is the result.