Forum Discussion

Jackofall's avatar
Jackofall
Icon for Resolver I rankResolver I
7 years ago
Solved

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.

  • 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

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion

    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.