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

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].