Forum Discussion

RvdHeijden's avatar
RvdHeijden
Post Prodigy
9 years ago
Solved

Transform a date to year-month

I have a question and i need your help.   I have a column with dates in the following format 3-2-2017 07:49:00 and i want to transform that to a year-weeknumber for example 2017-05.   My formula ...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    RvdHeijden

    Try this instead

    Year-Mon = FORMAT('Table'[Date], "YYYY-MM")

    Good Luck! :smileyhappy:

  • Sean's avatar
    Sean
    9 years ago

    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:

     

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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.

Sean's avatar
Sean
Community Champion
9 years ago

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:

 

  • RvdHeijden's avatar
    RvdHeijden
    Post Prodigy
    9 years ago

    Wow....thanks again Sean that did the trick :smileyhappy:

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      Sorry, but I'm afraid not. Probably the DAX week number does NOT return the ISO Week number (unless I'm terribly mistaking as I'm not a DAX expert).

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        It seems that you can supply 21 as second parameter to the WEEKNUM Function to obtain the ISO Week Number, see comments below Microsofts documenetation.

  • RvdHeijden's avatar
    RvdHeijden
    Post Prodigy
    9 years ago

    Sean

    i want the week to start on a monday so where should i place this variable in the formula ?

     

    Year-Wk = YEAR ( 'Calendar'[Date] ) & "-" & CONCATENATE ( IF ( WEEKNUM ( 'Calendar'[Date] ) < 10, "0", "" ), WEEKNUM ( 'Calendar'[Date] ) )

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      Refering to my previous post, I would guess

      Year-Wk = YEAR ( 'Calendar'[Date] ) & "-" & CONCATENATE ( IF ( WEEKNUM ( 'Calendar'[Date],21 ) < 10, "0", "" ), WEEKNUM ( 'Calendar'[Date] , 21) )

       

      But I'm not a DAX expert.

       

      Otherwise, in ISO you can't just take the year from the date, e.g. Sunday 1/1/2017 is week 2016-52 and Monday 12/31/2018 is week 2019-01.

       

      If you can just have my M-formula translated to DAX (or just use the M-solution), then you're good.