keep leading zero of convert data

[YuanG]

Hi,

I want to convert my date data in to week number and year like 2016-01, i've creat a column calsemaine = FORMAT('Merge date'[Date];"yyyy-ww"), but the result show with no leading zero of my week number. It works with month and day, i don't know why it can't with week.

Thanks in advance!

[Vvelarde · Accepted solution]

Hi @YuanG; a solution for this:

WeekNumberFormat =
IF (
    LEN ( FORMAT ( Calendario[Date]; "yyyy-ww" ) ) = 6;
    MID ( FORMAT ( Calendario[Date]; "yyyy-ww" ); 1; 5 ) & "0"
        & MID ( FORMAT ( Calendario[Date]; "yyyy-ww" ); 6; 2 );
    FORMAT ( Calendario[Date]; "yyyy-ww" 
)

[Vvelarde · Accepted solution]

Hi @YuanG; a solution for this:

WeekNumberFormat =
IF (
    LEN ( FORMAT ( Calendario[Date]; "yyyy-ww" ) ) = 6;
    MID ( FORMAT ( Calendario[Date]; "yyyy-ww" ); 1; 5 ) & "0"
        & MID ( FORMAT ( Calendario[Date]; "yyyy-ww" ); 6; 2 );
    FORMAT ( Calendario[Date]; "yyyy-ww" 
)

[Sean]

@KHorseman If I recall you had a post addressing this not with FORMAT but with an IF statement

I don't remember if it was M or DAX solution - and I can't seem to find that post? I'm pretty certain it was you though

Does it ring a bell?

[KHorseman · Accepted solution]

@Sean I know I did it for year-and-month using FORMAT. As far as I can remember, the only example for year-and-week that you might be thinking of would be if you actually took the time to read through that crazy query code I use for my very slow standard date table. That has a custom column called WeekNumber, which is...

= Int64.From(
	Text.From(
		Date.Year(
			Date.EndOfWeek([Date])
		)
	)
	&
	Text.PadStart(
		Text.From(
			Date.WeekOfYear(
				Date.EndOfWeek([Date])
			)
		),
		2,
		"0"
	)
)

That formula returns an integer value without the hyphen in the middle. You should be able to modify it pretty easily to remove the integer conversion and add a hyphen character to the concatenation.

In DAX it's...

WeekNum = IF(
	WEEKNUM(DateTable[Date]) < 10, 
	YEAR(DateTable[Date]) & "-0" & WEEKNUM(DateTable[Date]),
	YEAR(DateTable[Date]) & "-" & WEEKNUM(DateTable[Date])
)

[Greg_Deckler]

I would post this to the Issues forum.