Forum Discussion

Khomotjo's avatar
Khomotjo
Icon for Helper II rankHelper 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_WhsW...
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])

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper 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
    Icon for Helper II rankHelper II
    1 year ago

    Thanks rajendraongole1 

     

    I ve verified that all  columns are whole numbers :

    The above measure works

    • Khomotjo's avatar
      Khomotjo
      Icon for Helper II rankHelper 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.

       

       

      • rajendraongole1's avatar
        rajendraongole1
        Icon for Super User rankSuper User
        1 year ago

        Hi Khomotjo  - can you please change the Max to MAXX as like below measure.

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

         

        still if max is giving trouble , try with SELECTEDVALUE function. 

        I hope this works. please check