locked
How to convert Year to String RRS feed

  • Question

  • This Works:  CMisCurrentMonth = if('Date'[MonthNumber] = Month(Today()),"CM",'Date'[Month])

    I need to make this work: CYisCurrentYear = if('Date'[Year] = Year(Today()),"CY",'Date'[Year])

    The problem is "Date'[Year] is not a text string, "CY" is, and a calced field can't hold both.  I have tried variations of the Format(), but can't find one that will take:

    'Date'[Year], where it equals 2016, and return a string that equals "2016."  I have gotten other numbers, and they are indeed strings, just not one which corresponds with the year.

    Tuesday, December 13, 2016 12:00 AM

Answers

  • Hi Phil

    Either of these should work:

    CYisCurrentYear =
    IF ( 'Date'[Year] = YEAR ( TODAY () ), "CY", 'Date'[Year] & "" )

    CYisCurrentYear =
    IF ( 'Date'[Year] = YEAR ( TODAY () ), "CY", FORMAT ( 'Date'[Year], "0" ) )


    Owen Auger, CFA https://nz.linkedin.com/in/owenauger

    Tuesday, December 13, 2016 12:41 AM

All replies

  • Hi Phil

    Either of these should work:

    CYisCurrentYear =
    IF ( 'Date'[Year] = YEAR ( TODAY () ), "CY", 'Date'[Year] & "" )

    CYisCurrentYear =
    IF ( 'Date'[Year] = YEAR ( TODAY () ), "CY", FORMAT ( 'Date'[Year], "0" ) )


    Owen Auger, CFA https://nz.linkedin.com/in/owenauger

    Tuesday, December 13, 2016 12:41 AM
  • Thank you very much.  That was the magic I was looking for.
    Tuesday, December 13, 2016 12:47 AM