Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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 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]) )
Proud to be a Super User!
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"
)
@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?
@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]) )
Proud to be a Super User!
I would post this to the Issues forum.
User | Count |
---|---|
106 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
103 | |
96 | |
74 | |
67 |