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

RvdHeijden

Try this instead

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

Good Luck! :smileyhappy:

  • RvdHeijden's avatar
    RvdHeijden
    Icon for Post Prodigy rankPost Prodigy
    9 years ago

    Sean

    Your 2 for 2, thanks a bunch, this works

  • jleibowitz's avatar
    jleibowitz
    Regular Visitor
    9 years ago

    Hi Sean,

     

    I am trying to create a custom column to extract year/month from my date which is currently in the following format: 01/01/2017 but when I use the formula below, I recieve a 'Token Literal Expected' error for the 'Table' piece. 

     

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

     

    Also, if I try and use basic functions such as CONCATENATE, FORMAT, MONTH, YEAR, etc, I get an error that Power BI does not recognize the function. Thanks for the advice!

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    How is this supposed to be used in the context of the query? 

    I tried adding this to a Table.AddColumn() clause as the last argument and it doesnt recognised FORMAT, What am I missing?