Assistance with calculation that uses date field

[Brysonds]

Hi Everyone,

I just started using Power BI this week and it's been quite the adventure so far - very cool tool!!

I created two columns (one for Year, and one for Month) based on my [Date] field using these formulas:

Year = FORMAT('Sales and Margin Data'[Date], "yyyy")

Month = FORMAT('Sales and Margin Data'[Date], "mmmm")

These new fields work great as slicers, etc.  However, if I try to reference these new fields in a measure formula, I get an error that they are not of type [Date] fields. 

Is there any way to fix this? I don't want to use the real [Date] field as it limits functionality in slicers, and has no hierachy.

[v-sihou-msft]

@Brysonds

In DAX, when using the FORMAT() function, it will return text instead of date. So when you want to use it in time intelligence functions, it will throw the data type error. So if you need to use the date for calculation, just directly use the [Date] column.

Regards,

[Brysonds]

Hi Simon,

Thank you very much for the clarification. However, one of the main reasons I am trying to avoid the date field is because it's missing a date hierarcy (it only gives me year). 

Is there a way to change the FORMAT() fields from text to date?