Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

New column from date until variable date + conditions

Hi all,

 

I need to create a new column that is either 0 or 1 based on some criteria:

If date named posting date is equal to or greater than 18-01-2021

and If category named initiative <> "active"

and If category named  items = "finetune"

If all criteria is met then 1 else 0

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, Anonymous ;

    you could use M lanuage by the following:

     

    if [Posting Date]>=#date(2021,1,18) and [Price Initiative]="Finetune" and [Initiative Period]<>"Active"then 1 else 0

     

    and another custom column as follows:

    let _a=[Business Unit]
in List.Min(
    Table.SelectRows(#"Changed Type",each [Initiative Period] = "Active"
            and [Price Initiative] = "Finetune" and [Business Unit]=_a)[Posting Date])

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

     

4 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    you can try the following calculated column:

    New Column =
IF(
      myTable[Date] >= DATE(2021,01,18)
    && myTable[category named initiative] <> "active"
    && myTable[category named items] = "finetune",
    1,
    0
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks, I think it worked.

      However i have this other column that was created using a measure and not created inside the edit query, which now doesn't work anymore.

      Could you please help me also create a custom column with these criteria:

      Min. Date Active Finetune =
      MINX (
      FILTER (
      Initiativemonitor_table,
      EARLIER ( Initiativemonitor_table[Business Unit] ) = Initiativemonitor_table[Business Unit]
      && Initiativemonitor_table[Initiative Period] = "Active"
      && Initiativemonitor_table[Price Initiative] = "Finetune"
      ),
      Initiativemonitor_table[Posting Date]
      )


      Basically, I need to have a column that writes the minimum posting date where the criteria is met:
      Initiativemonitor_table[Price Initiative] = "Finetune"
      Initiativemonitor_table[Initiative Period] = "Active"

      per business unit.

      So for different business units there would be a different minimum posting date.
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    There is still something wrong with the custom formula.. =/

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi, Anonymous ;

    you could use M lanuage by the following:

     

    if [Posting Date]>=#date(2021,1,18) and [Price Initiative]="Finetune" and [Initiative Period]<>"Active"then 1 else 0

     

    and another custom column as follows:

    let _a=[Business Unit]
in List.Min(
    Table.SelectRows(#"Changed Type",each [Initiative Period] = "Active"
            and [Price Initiative] = "Finetune" and [Business Unit]=_a)[Posting Date])

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.