Forum Discussion

Mario_Nistal's avatar
Mario_Nistal
New Member
4 years ago
Solved

Wrong Max date returned

Hello everyone, my data set has these columns:   But when I am creating a measure: MaxDate = MAX(Query1[Soak Start Date])   This is what I am getting:   Any hints and help to help ...
Tutorial Requests
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi Mario_Nistal ,

    You can not use DateTime format and Date format at the same time in a column in Power BI Desktop, it would be changed to text type.

    You need to change the type to both datetime or both date type, then create the previous measure to get the MaxDate.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZBLjsMwDEOvUmQ9gER9aoe7HmCA2Qe5/zXq1COnKwN6FknxODaI7WJqeDhdOd6/3+38OTaTXnNzZjxeNQcmgFGD0NpwgU0SjKTbF4gJnlRj9NJyMV9asRPxRdokOaa0XlohqBVQk7nEUqxMsjGt5k3+laBX3tAbWK+8aYTfwHXFsmGzFxn/o1Z0Z+AGjnIPJ7LAKPc5QaePQ5Y7dNU1gl0VL5er4Jyo0ZPR6vqxgb5I0FcvkI+/bef5Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Soak Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Soak Start Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Soak Start Date", type datetime}}, "en-US")
in
    #"Changed Type with Locale"

    If you do not want to show time, you can change the format to short date:

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-yingjl's avatar
v-yingjl
Community Support
4 years ago

Hi Mario_Nistal ,

You can not use DateTime format and Date format at the same time in a column in Power BI Desktop, it would be changed to text type.

You need to change the type to both datetime or both date type, then create the previous measure to get the MaxDate.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZBLjsMwDEOvUmQ9gER9aoe7HmCA2Qe5/zXq1COnKwN6FknxODaI7WJqeDhdOd6/3+38OTaTXnNzZjxeNQcmgFGD0NpwgU0SjKTbF4gJnlRj9NJyMV9asRPxRdokOaa0XlohqBVQk7nEUqxMsjGt5k3+laBX3tAbWK+8aYTfwHXFsmGzFxn/o1Z0Z+AGjnIPJ7LAKPc5QaePQ5Y7dNU1gl0VL5er4Jyo0ZPR6vqxgb5I0FcvkI+/bef5Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Soak Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Soak Start Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Soak Start Date", type datetime}}, "en-US")
in
    #"Changed Type with Locale"

If you do not want to show time, you can change the format to short date:

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.