New column from date until variable date + conditions

[Anonymous]

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

[v-yalanwu-msft · Accepted solution]

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.  

[Anonymous]

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

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

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.