Forum Discussion

AvPowerBI's avatar
AvPowerBI
Icon for Post Patron rankPost Patron
4 years ago
Solved

Include 0 for month number where applicable

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

2 Replies