Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Create Date column from Year and Month columns

I have a table with a column of Month NUmber and a column for Year. I want to make a date column for the 1st of the month. The new column I want to be formatted [Month Number]/1/[Year]

Example: the second row is February 2021. I want the new date column to look like 2/1/2021.

smpa01
4 years ago
Anonymous this was to be done in PQ.

For DAX use this

Column = DATE('Table'[Year],'Table'[Month Number],1)

10 Replies

smpa01
Community Champion
4 years ago
Anonymous create a new column as

#date([Year],[Month Number],1)

and then format as you wish through DAX
- Anonymous
  Not applicable
  4 years ago
  I get this error when adding the custom column.
  - ronrsnfld
    Super User
    4 years ago
    date <> #date
    
    change
    
    date([Year],[Month Number],1)
    
    to (as written by smpa01 )
    
    #date([Year],[Month Number],1)
smpa01
Community Champion
4 years ago
Anonymous this was to be done in PQ.

For DAX use this

Column = DATE('Table'[Year],'Table'[Month Number],1)
- Maitreya10
  Frequent Visitor
  2 years ago
  Here for the last date or maximum date, I want the last day of month instead of first day, eg if my data lies between Jan 2023 to Jan 2024, I want all records between Jan 2023 to Dec 2023 to show date for first day of month(MMM/01/YYYY) except the records having date in Jan 2024 which I want to show as last day of month(01/31/2024).
  - dufoq3
    Community Champion
    2 years ago
    Hi, last day of month you can achieve this way:
    
    Date.EndOfMonth(#date([Year],[Month Number],1))

Related content