Forum Discussion

mike_asplin's avatar
mike_asplin
Helper V
5 months ago
Solved

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???...
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    5 months ago

    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

     

     

grazitti_sapna's avatar
grazitti_sapna
Super User
5 months ago

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!

  • mike_asplin's avatar
    mike_asplin
    Helper V
    5 months ago

    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?

    • grazitti_sapna's avatar
      grazitti_sapna
      Super User
      5 months ago

      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

       

       

      • mike_asplin's avatar
        mike_asplin
        Helper V
        5 months ago

        Ok that odd as did have it formated as a datetime column, but seems ok now without the FORMAT added to the measure.

         

        Thanks for your help