Condition formula for LTMD Flag

[Haitham_87]

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 or equal to the day in the subsequent cell, it should display "Yes"; otherwise, it should show "No."

I have attempted several methods, but none have worked for me. The issue seems to be that using the MAX date or LAST DATE functions returns the last date of the year.

Could anyone guide me?

End Date	LTMD
Jun 9, 2024	YES
May 22, 2024	NO
May 17, 2024	NO
May 16, 2024	NO
May 3, 2024	YES
Apr 18, 2024	NO
Apr 3, 2024	YES
Apr 1, 2024	yes
Mar 22, 2024	NO
Mar 19, 2024	NO
Mar 18, 2024	NO
Mar 8, 2024	YES
Feb 29, 2024	NO
Feb 29, 2024	NO
Feb 22, 2024	NO
Feb 21, 2024	NO
Feb 21, 2024	NO
Feb 19, 2024	NO
Feb 19, 2024	NO
Feb 9, 2024	YES
Feb 5, 2024	YES
Jan 16, 2024	NO
Jan 9, 2024	YES
Jan 9, 2024	YES
Jan 9, 2024	YES

[Haitham_87]

Hi,

these are no because the highest date is 9-Jun-24 so the day will be 9 and I need to flag any day <=9 with Yes, and if it greater that 9 it will be NO, I want this column so I can show the MTD sales in each month and make a compersion 

[dufoq3 · Accepted solution]

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]

thanks alot I will try it and keep you updated

[v-cgao-msft]

Hi @Haitham_87 ,

// This column should be based on the logic that if the day of the maximum date in the "End Date" column is greater than or equal to the day in the subsequent cell, it should display "Yes"; otherwise, it should show "No."

Could you explain why these records also return “No”? It seems that their dates are also greater than or equal to the next date:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group