Forum Discussion
Post ProdigyTransform a date to year-month
RvdHeijdenHere's DAX
Year-Wk = YEAR ( 'Calendar'[Date] ) & "-" & CONCATENATE ( IF ( WEEKNUM ( 'Calendar'[Date] ) < 10, "0", "" ), WEEKNUM ( 'Calendar'[Date] ) )Yes you may have to adjust WEEKNUM depending on when your week starts SUN (1) on MON (2)
I have skipped this optional parameter because the default is 1 which is what i need.
Hope this helps.
Good Luck!:smileyhappy:
Community ChampionJust in case you mean week number (as you mention in your example) instead of month number, and because I spot some Dutch in your coding, the code below is a M function (Power Query / Query Editor) that wll return the ISO Week Number, based on a date.
let
Source = (DateParameter as date) => let
Thursday = Date.AddDays(DateParameter,3-Date.DayOfWeek(DateParameter,Day.Monday)),
Jan1 = #date(Date.Year(Thursday),1,1),
Days = Number.From(Thursday - Jan1),
Result = Number.RoundDown(Days/7)+1
in
Result
in
Source
This will only return the week number.
If you want year-week number, replace the "Result = ..." line by:
Result = Text.From(Date.Year(Thursday))&"-"&Text.PadStart(Text.From(Number.RoundDown(Days/7)+1),2,"0")
- RvdHeijden
MarcelBeug your right, i was to fast and however Sean formula works it returns year-month instead of year-weeknumber
But i do not fully understand your formula Marcel.
Im a relative nooby as it comes to these types of formula. Can you give me the complete formula so i can copy/paste is in PowerBi ?
- MarcelBeug
Well, the formula is actually incorporated in the funtion.
I'm not experienced with DAX, but I can provide the rules for ISO week number and the translation of the function steps in English.
Rules for ISO week number:
1. First day of the week is Monday.
2. Each week from Monday to Sunday has the same week number.
3. Week 1 of the year is the week with the first Thursday of the year,
meaning more than half of the days of the week are in week 1.
If January 1st is on Monday-Wednesday 0-2, it will be week 52 or 53 of the preceding year.
If January 1st is on Thursday-Sunday 3-6, it will be week 1 of the current year.Steps to determine ISO week number:
1. Determine the date of Thursday of the current week.
2. Determine January 1st of the date from step 1.
3. Calculate the number of days from January 1st to the date from step 1.
4. Divide the number of days from step 3 by 7, round down and add 1.
SeanRvdHeijdenHere's DAX
Year-Wk = YEAR ( 'Calendar'[Date] ) & "-" & CONCATENATE ( IF ( WEEKNUM ( 'Calendar'[Date] ) < 10, "0", "" ), WEEKNUM ( 'Calendar'[Date] ) )Yes you may have to adjust WEEKNUM depending on when your week starts SUN (1) on MON (2)
I have skipped this optional parameter because the default is 1 which is what i need.
Hope this helps.
Good Luck!:smileyhappy:
