Forum Discussion

koorosh's avatar
koorosh
Post Partisan
4 years ago
Solved

YearMonth Column

Hello All, Why I can not create the YearMonth column in the linked file through the following DAX : Date = FORMAT(DATE('Table'[YERR],'Table'[Month],1),"MM-YYYY") How I can create it?   Regards h...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    You will need to use following

    = FORMAT(DATE(YEAR('Calendar'[Year]),'Calendar'[Month Number],1),"MM-YYYY")

    In your YEAR column, you have used STARTOFYEAR which gives a date. But in DATE, it needs a number for YEAR. YEAR('Calendar'[Year]) provides that year number.

    Second problem was that you were using month name as second argument for Date whereas it needed a number 1-12 which should have been provided through 'Calendar'[Month Number].

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Looks like you have misspelled Year as YERR that is why you must be getting error.

Since, your pbix file refers to a csv file, hence data is not visible to me. But you can always refer to date field itself. i.e. even following would work

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

 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Use this

    Year Month = FORMAT('Calendar'[Date],"MM-YYYY")

    • koorosh's avatar
      koorosh
      Post Partisan
      4 years ago

      Where is the problem with the following, please ?

      MonthYear = FORMAT(DATE('Calendar'[YEAR],'Calendar'[Month Name],1),"MM-YYYY")
       

       

       

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        You will need to use following

        = FORMAT(DATE(YEAR('Calendar'[Year]),'Calendar'[Month Number],1),"MM-YYYY")

        In your YEAR column, you have used STARTOFYEAR which gives a date. But in DATE, it needs a number for YEAR. YEAR('Calendar'[Year]) provides that year number.

        Second problem was that you were using month name as second argument for Date whereas it needed a number 1-12 which should have been provided through 'Calendar'[Month Number].