Forum Discussion

DataFun's avatar
DataFun
Icon for Helper I rankHelper I
2 years ago

DAX Date Table fields formatted as date

I have a DAX Date table (code below) and I want to use trendlines and forecast on a line chart, however, the format of these fields is TEXT.  If I tried to set the Data Type to DATE in Power BI, I receive an error stating it "can't convert type Text to type Date".  Since these field have a data type of TEXT, the Trendline and Forecast options are not available.   I want to be able to do trends and forcasts by mmm-yy and q#-yyyy.  How can I create my date taple to ensure the fields have data type DATE? Thank you.

 

DAX Date Table Code

Z_Date =
Var MinDate = YEAR (Min(TicketData[Creation]))
Var MaxDate = YEAR (MAX(TicketData[Creation]))
RETURN
Addcolumns (
    FILTER (
        CALENDARAUTO(),
        YEAR ([Date]) >= MinDate &&
        YEAR ([Date]) <= MaxDate
    ),
    "Month Number", Month([Date]),
    "Month", Format([Date], "mmm"),
    "Qtr", Format([Date], "\QQ"),
    "Quarter", Format([Date], "\QQ\-YYYY"),
    "Year", Year([Date]),
    "Month-Year", Format([Date], "mmm-yy"),
    "Date Sort", Format([Date], "yyyymmdd"),
    "Month Sort", Format([Date], "yyyymm"),
    "Quarter Sort", Format([Date], "YYYY\0Q")
    )
Need Help

2 Replies

  • DataFun's avatar
    DataFun
    Icon for Helper I rankHelper I
    2 years ago

    , I understand that.  My question was regarding whether their is a way to format the DAX info as a date instead of text.  For example, if I want to use the quarter for my forecast, can I format my DAX Quarter filed as a date instead of text.  The format of this field is Q1-2024, Q2-2024, etc.