cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jackofall Regular Visitor
Regular Visitor

Date Table, add a text column as well

This works well but I want one column to come out as text so I don't have to change it afterwards.

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"PeriodMånad"; FORMAT ( [DATE]; "YYYYMM" );
"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" ))

I want "PeriodMånad"; FORMAT ( [DATE]; "YYYYMM" ); to be text or integer from start. I tried Date.ToText but that is M not DAX.

I am thinking

"PeriodMånad"; MagigTextFunction(FORMAT ( [DATE]; "YYYYMM" ));

FORMAT only handles dates and number. Finally I tried this.

"PeriodMånad"; TRIM(FORMAT ( [Date]; "YYYYMM" ));

  Now the column comes out as Text from the start but it feels awkward.

 

Is there a corresponding way of creating a date table in M from the bat? I am not fond of doing this in the end of my modelling.

1 ACCEPTED SOLUTION

Accepted Solutions
themistoklis New Contributor
New Contributor

Re: Date Table, add a text column as well

@Jackofall

 

You dont need to create a custom calendar using DAX.

 

There are already custom calendars, created using Power Query, available online.

You can also download the customer calendar from this link.

 

Obviously you can add more info to it if you want

 

If you want to move it across reports just simply copy and paste it.

 

 

2 REPLIES 2
themistoklis New Contributor
New Contributor

Re: Date Table, add a text column as well

@Jackofall

 

You dont need to create a custom calendar using DAX.

 

There are already custom calendars, created using Power Query, available online.

You can also download the customer calendar from this link.

 

Obviously you can add more info to it if you want

 

If you want to move it across reports just simply copy and paste it.

 

 

Jackofall Regular Visitor
Regular Visitor

Re: Date Table, add a text column as well

Thanks I will look at it.