DAX - Extract Date from TimeStamp (contain date & time)

[Anonymous]

Dear Experts,

Which formula I can use to extract date from time stamp contains date & time?

Table looks like this on the table 'Date' [Date]

Date

1/1/2018 12:00:00AM

DAX being tried: DATE('Date'[Date])  --> no result.

Can brief me any easy fix for this?

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Please check what data type of your Date column firstly.

If your Date column is Date/Time type in Power BI, you only need to change the Data format to be M/d/yyyy under Modeling.

If your Date column is Text type in Power BI, you could create the measure with the fomula below.

Measure 2 = LEFT(MAX('Table2'[Date]),9)

Here is the result.

Best Regards,

Cherry

[almirneral]

A função TRUNC resolve isso e é bem mais fácil de usar. Precisa apenas alterar o tipo de dados posteriormente

[tsmiley]

after some trial and error this worked;

DateTime.FixedLocalNow()

[nadavsn]

try [DateTime].Date

Works for me.

[Manuel-Levo]

I know this is an old answer, but this is only calling the related/hidden date column which is not really suggested.

[sujith31]

This worked for me. Thanks!

[Ashish_Mathur]

Hi,

Try this calculated column formula

=INT('Date'[Date])

Format this column as Date.

[maclura]

Hi @Ashish_Mathur,

 clean and neat, the best solution for me.

m

[Ashish_Mathur]

Thank you.

[Anonymous]

Hi Ashish & all experts here,

Thanks for all your advice, i guess i have to check on the source of my 'Date' [Date] table column, because in the formula, it has the red underlined. 

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Please check what data type of your Date column firstly.

If your Date column is Date/Time type in Power BI, you only need to change the Data format to be M/d/yyyy under Modeling.

If your Date column is Text type in Power BI, you could create the measure with the fomula below.

Measure 2 = LEFT(MAX('Table2'[Date]),9)

Here is the result.

Best Regards,

Cherry

[Anonymous]

Another solution, if you want it to remain a date object and not text is to do something like this:

-Create a new COLUMN

DateColumn = Date[timestamp].[Date]

Then click on the new object and format it to the desired format.

[Anonymous · Accepted solution]

@v-piga-msft I don't have access for modelling the SQL database.

The measure looks legit, I will try on it. Thank you 🙂

[dujos]

Hello Hwang, see in the picture below the result.

IMPORTANT use a calculated Column to FORMAT DAX function works fine.

sorry the menus are in portuguese but i think you can identify the itens easy

and the date format fuction uses FORMAT(table[field];"mm/dd/yyyy")

Good Luck!!!

[Anonymous]

Hi Dujos,

The problem is i don't have access to the modelling tab, because its a company SQL BI cube.

I'm only able to modify the visual and and make DAX measure in the report.

I have tried above solution with FORMAT( 'Date'[Date],"dd/mm/yyyy)*1 but it also doesn't provide any result.

[Anonymous]

=FORMAT('Date'[Date], "dd/mm/yyyy")*1

[Anonymous]

 Hi,

Format doesn't work. Any idea? Below is the view pop up.

https://drive.google.com/file/d/19poSGC_YQfRr4fMfutQ5YDC9njGK4vtR/view?usp=sharing