Include 0 for month number where applicable

[AvPowerBI]

Hi,

I currently have the below calendar.

Dates = 
    VAR MinYear = YEAR ( MIN ('Fact'[Employment Start Date] ) )
    VAR MaxYear = YEAR ( MAX ('Fact'[Employment End Date] ) )
    RETURN
    ADDCOLUMNS (
        FILTER (
            CALENDARAUTO( ),
            AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
        "Year", FORMAT([Date], "yyyy"),
        "Date2",FORMAT( [Date], "dd/mm/yyyy" ),
        --"Calendat Year", "CY " & YEAR ( [Date] ),
        "Month Name", FORMAT ( [Date], "mmm" ),
        "Month Number", MONTH ( [Date] ),
        "Weekday", FORMAT ( [Date], "dddd" ),
        "Weekday number", WEEKDAY( [Date] ),
        "Year Month", FORMAT ( [Date], "mmm yy"),
        "Month-Year", FORMAT ( [Date], "mmm-yyyy"),
         "Month-Year 2", FORMAT ( [Date], "mmm,yy"),
         "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
        )

The column  "Month Number", MONTH ( [Date] ), is show the months between January to September as a single number i.e 1 to 9

I require a zero in front so it shows as 01, 02, 03 etc.. 

I tried to add a calculated column but it shows up as an Error on the result set

Month =

IF(

LEN(Dates[Month Number])=1, "0" & Dates[Month Number], Dates[Month Number]

)

and then once I have the calculated column Month created I then wanted to create another calculated column that has a date for start of the month like this one 

Start of Month = "01-" & Dates[Month] & "-" & Dates[Year]

But this will not work because the Month column is currently showing #ERROR

Thanks

[v-zhangti]

Hi, @AvPowerBI 

You can check the following methods.

Month = FORMAT(MONTH(Dates[Date]),"00")

Start of Month = "01-"&[Month]&"-"&[Year]

More information about time and date formats can be found in the following links.

https://docs.microsoft.com/dax/format-function-dax 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@AvPowerBI , try like

format(MONTH ( [Date] ), "00")