Forum Discussion

YuanG's avatar
YuanG
Icon for Helper I rankHelper I
10 years ago
Solved

keep leading zero of convert data

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 w...
Need Help
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    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" 
    )

  • Anonymous's avatar
    Anonymous
    10 years ago

    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])
)

     

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
10 years ago

Anonymous If I recall you had a post addressing this :smileyhappy: 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?

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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])
)