Date Function Error

[Khomotjo]

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()

)

[v-junyant-msft]

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.

[rajendraongole1]

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]

Thanks @rajendraongole1 

I ve verified that all  columns are whole numbers :

The above measure works

[Khomotjo]

@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]

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]

Thanks @rajendraongole1 @v-junyant-msft  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,

[rajendraongole1]

Hi @Khomotjo - To group the data by date and calculate the total orders finalized for each day (ignoring the time component), you can create a measure in Power BI that extracts only the date from the datetime field as like below:

Total Orders by Date =
CALCULATE(
COUNT('YourTable'[OrderID]),
ALLEXCEPT('YourTable', 'YourTable'[DateOnly])
)

you need to replace 'YourTable'[DateOnly] with a column created using the date part of your datetime column.

[Khomotjo]

Thanks @rajendraongole1   Is there another way to create a column using only the date part without using  DATE() or formating directly from the tool bar( to make the date-time a short date)?

[rajendraongole1 · Accepted solution]

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])