Reply
Regular Visitor
Posts: 37
Registered: ‎06-08-2017
Accepted Solution

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:

dw-dimdate.png

 

I was able to create a calendar table with the code below, but I can not find a way to add this additional line.

Can you help me?

 

Calendar = 
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" )
)

Accepted Solutions
Highlighted
Regular Visitor
Posts: 38
Registered: ‎09-20-2017

Re: Insert Unknown Date into Calendar Table

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!

View solution in original post


All Replies
Highlighted
Regular Visitor
Posts: 38
Registered: ‎09-20-2017

Re: Insert Unknown Date into Calendar Table

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!

Regular Visitor
Posts: 37
Registered: ‎06-08-2017

Re: Insert Unknown Date into Calendar Table

It worked exactly as I wanted it, thanks!

 

This is the result.

 

Capturar.PNG