Forum Discussion
MAX datetime truncates to date???
Wasnt expecting this. I have single cell table for the refesh time which is dd/mm/yyyy 00:00:00 format
to use it on a card I converted it to a measure but it comes out just dd/mm/yyyy??? Even itf i format it still cut off the time
Last Refresh = FORMAT(VALUES('Refresh Time'[Refresh Time]),"dd/mm/yy 00:00")
How do you preven this? thanks
Hi mike_asplin,
When you use MAX('Refresh Time'[Refresh Time])
Power BI returns a DateTime value internally, but:
- The Card visual auto-formats it
- And often defaults to showing only the date part
To avoid this
Select your column 'Refresh Time'[Refresh Time], change format to dd MMM yyyy HH:mm:ss
now you can use it in MAX and you should get the correct results.
If not then Use Format
If the issue is fixed, Mark it as accepted solution
6 Replies
Hi mike_asplin,
You are actually hardcoding it by providing 00:00.
Use below DAX instead
Last Refresh =
FORMAT(
MAX('Refresh Time'[Refresh Time]),
"dd MMM yyyy HH:mm"
)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!well that was dumb of me!!!
I only added the format because when I did MAX('Refresh Time'[Refresh Time]) I just got the date. Is that expected and you have to add the FORMAT bit?
Hi mike_asplin,
When you use MAX('Refresh Time'[Refresh Time])
Power BI returns a DateTime value internally, but:
- The Card visual auto-formats it
- And often defaults to showing only the date part
To avoid this
Select your column 'Refresh Time'[Refresh Time], change format to dd MMM yyyy HH:mm:ss
now you can use it in MAX and you should get the correct results.
If not then Use Format
If the issue is fixed, Mark it as accepted solution
Hi,
I use below in Blank Query for adding Last Refresh date:
let IST_TimeZone = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 5.5), IST_Time = DateTimeZone.RemoveZone(IST_TimeZone), IST_Text = DateTime.ToText(IST_Time, "dd-MMM-yyyy hh:mm tt", "en-IN"), #"Converted to Table" = #table(1, {{IST_Text}}), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "LastRefresh"}}) in #"Renamed Columns"You can tweak around to show your desired format and timezone. But this is a saviour - if it helps!
Thats the column isnt an issue its turning it inot a measure to put it on card, but thanks anyway
