Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost 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:

     

Sean's avatar
Sean
Icon for Community Champion rankCommunity 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
Icon for Post Prodigy rankPost 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
    Icon for Community Champion rankCommunity 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.