Forum Discussion

Khomotjo's avatar
Khomotjo
Helper II
1 year ago
Solved

Date Function Error

Hello Evveryone, I am trying to construct a date using the Date function. My data looks like this :

 

My calculated coloumn looks like this : 

 

WF_Finalised = DATE (StockMovements[P9_WhsWorkFlowFinalisedDateYear],StockMovements[Month_Number],StockMovements[P9_WhsWorkFlowFinalisedDateDay])

 

I am getting below error , I have confirmed I do not have any blanks in the year/month and day columns :

I am using direct query in BI. There are no cyclic dependecies. The only other calculated coloumn in the month is the month date which I calculated from the month name using :

 

MonthNumber = SWITCH(
    TRUE(),
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "January", 1,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "February", 2,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "March", 3,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "April", 4,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "May", 5,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "June", 6,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth]= "July", 7,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "August", 8,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "September", 9,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "October", 10,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "November", 11,
    StockMovements[P9_WhsWorkFlowFinalisedDateMonth] = "December", 12,
    BLANK()
)
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi Khomotjo  - Yes, you can create a new column in Power BI using DAX that extracts only the date part from a date-time column without using DATE() or formatting it directly from the toolbar. You can use the TRUNC function in DAX.

     

    Take a new column 

    DateOnly = TRUNC('TableName'[DateTimeColumn])

9 Replies

Replies have been turned off for this discussion
  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Khomotjo  - Can you please verify that the StockMovements[P9_WhsWorkFlowFinalisedDateYear], StockMovements[Month_Number], and StockMovements[P9_WhsWorkFlowFinalisedDateDay] columns are of a numeric data type. The DATE function requires all arguments to be numbers.

    If any of these columns are in text format, convert them to numbers using the VALUE function.

    In DirectQuery mode, calculated columns might encounter limitations depending on the underlying data source or query structure.

    can you also create measure calculation

    TestDateMeasure =
    DATE(
    MAX(StockMovements[P9_WhsWorkFlowFinalisedDateYear]),
    MAX(StockMovements[Month_Number]),
    MAX(StockMovements[P9_WhsWorkFlowFinalisedDateDay])
    )

     

    If the measure works, the issue might be related to calculated column restrictions in DirectQuery.

      • Khomotjo's avatar
        Khomotjo
        Helper II
        1 year ago

        rajendraongole1  I just noticed that this measure returns  31 December 2025 which is incorrect. It seems that the measure caculates the maximum date in the current year. The max date is 22 January 2025.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Khomotjo ,

    Thanks for rajendraongole1's reply!
    And Khomotjo , The document mentions that DATE function is not supported for use in DirectQuery mode when used in calculated columns:
    https://learn.microsoft.com/en-us/dax/date-function-dax#remarks 

    In addition, the method provided by rajendraongole1 is a measure. When the measure is placed in a visual object, it will be affected by the context and the returned results will be different. When you place the measure directly in the visual object without other fields, it will select the maximum value each of the column in the Year, Month, and Day columns and form the date instead of returning the maximum value after the date is formed. 



    You can refer to the following example:

    Measure = 
DATE(
    MAX('Table_1'[Year]),
    MAX('Table_1'[Month]),
    MAX('Table_1'[Day])
)

    If you want to get the maximum date after each row of data is combined into a date, you can add another measure:

    Max_Date = MAXX(ALL(Table_1), [Measure])

     

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.