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 week number. It works with month and day, i don't know why it can't with week.

 

Thanks in advance!

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

     

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    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" 
    )

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