Month name from date column

Question

I have a column with date format, this kind of columns usually have a hierarchy with day, month and year to filter by but I'm using a direct query from my database and this doesn't appear.

I've tried to split columns but when I try to split a column by name month this returns me an error because this kind of action is not compatible with a direct query.

Then I thought that one way to solve my problem as I can't do changes in columns it could be to make a Dax Function that extract month and year from my column

This is the Column I'm talking about: This is the error when i try split columns by delimiter:

I've tried with MONTH function but this retuns me an error:

Have you tried using YEAR, MONTH, DAY functions, or FORMAT to create calculated columns in DAX? — OscarLar
Comment of @OscarLar is useful: When you need it for calculations, you need to use MONTH & YEAR. When you only want to show it to end user, you can use FORMAT. — Aldert

Dan Dan · Accepted Answer · 2019-08-05T09:16:09

In DirectQuery mode, there's also a number of restrictions to the DAX functions available. For example, the FORMAT function does not work within a calculated column. However, the MONTH, YEAR and DAY functions work just fine, so in order to create a column with the month names, you could use this:

SWITCH ( MONTH ( [FechaAuditoria] ),
    1 , "January" ,
    2 , "February" ,
    3 , "March" ,
    ...
)

Month name from date column

1 Answers