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 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 DateLTMD
Jun 9, 2024YES
May 22, 2024NO
May 17, 2024NO
May 16, 2024NO
May 3, 2024YES
Apr 18, 2024NO
Apr 3, 2024YES
Apr 1, 2024yes
Mar 22, 2024NO
Mar 19, 2024NO
Mar 18, 2024NO
Mar 8, 2024YES
Feb 29, 2024NO
Feb 29, 2024NO
Feb 22, 2024NO
Feb 21, 2024NO
Feb 21, 2024NO
Feb 19, 2024NO
Feb 19, 2024NO
Feb 9, 2024YES
Feb 5, 2024YES
Jan 16, 2024NO
Jan 9, 2024YES
Jan 9, 2024YES
Jan 9, 2024YES
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

4 Replies

  • 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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Haitham_87's avatar
    Haitham_87
    New Member
    2 years ago

    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