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_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
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

Thanks rajendraongole1 

 

I ve verified that all  columns are whole numbers :

The above measure works

  • 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.

     

     

    • rajendraongole1's avatar
      rajendraongole1
      Super 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

      • Khomotjo's avatar
        Khomotjo
        Helper II
        1 year ago

        Thanks rajendraongole1 Anonymous  I am trying to get one date for each of the multiple date entries in the data. For example on the 22 January I might have 3 orders finalised at 11:00, 12:00 and 13:00. The data will will show all 3 with their respective date time records.  I  want to calculate the total orders finalised on the 22 January, the issue is the model considers  these 3 as separate because of the time. I tried to format the date from the tool bar( change to short date) but when I pull the table it still considers the time. It looks like this :

        I want to see only 1 entry for each of the dates in the data,