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

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.