Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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
@v-piga-msft I don't have access for modelling the SQL database.
The measure looks legit, I will try on it. Thank you 🙂
A função TRUNC resolve isso e é bem mais fácil de usar. Precisa apenas alterar o tipo de dados posteriormente
after some trial and error this worked;
DateTime.FixedLocalNow()
try [DateTime].Date
Works for me.
I know this is an old answer, but this is only calling the related/hidden date column which is not really suggested.
This worked for me. Thanks!
Hi,
Try this calculated column formula
=INT('Date'[Date])
Format this column as Date.
Thank you.
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.
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
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.
@v-piga-msft I don't have access for modelling the SQL database.
The measure looks legit, I will try on it. Thank you 🙂
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!!!
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.
=FORMAT('Date'[Date], "dd/mm/yyyy")*1
Hi,
Format doesn't work. Any idea? Below is the view pop up.
https://drive.google.com/file/d/19poSGC_YQfRr4fMfutQ5YDC9njGK4vtR/view?usp=sharing
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |