Forum Discussion

Haitham_87's avatar
Haitham_87
New Member
2 years ago
Solved

Condition formula for LTMD Flag

Hi everyone, I need your help creating a new Power BI column for an "LTMD Flag." This column should be based on the logic that if the day of the maximum date in the "End Date" column is greater than...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Haitham_87, check this:

     

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8irNU7DUUTAyMDJRitWJVvJNrFQwMkITMDRHFzBDEzBG4jsWFCkYWqAJYChAMaAI3U6gAkt0AQs0AWS+W2qSgpElQQEjdAFDQgKG6GZgCKDzTZH4Xol5qGEFErAklh8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"End Date", type date}}),
    LatestDateDay = Date.Day(List.Max(ChangedType[End Date])),
    Ad_LTMD = Table.AddColumn(ChangedType, "LTMD", each if LatestDateDay >= Date.Day([End Date]) then "YES" else "NO", type text)
in
    Ad_LTMD
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Haitham_87, check this:

 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8irNU7DUUTAyMDJRitWJVvJNrFQwMkITMDRHFzBDEzBG4jsWFCkYWqAJYChAMaAI3U6gAkt0AQs0AWS+W2qSgpElQQEjdAFDQgKG6GZgCKDzTZH4Xol5qGEFErAklh8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"End Date", type date}}),
    LatestDateDay = Date.Day(List.Max(ChangedType[End Date])),
    Ad_LTMD = Table.AddColumn(ChangedType, "LTMD", each if LatestDateDay >= Date.Day([End Date]) then "YES" else "NO", type text)
in
    Ad_LTMD
  • Haitham_87's avatar
    Haitham_87
    New Member
    2 years ago

    thanks alot I will try it and keep you updated