IF ELSE for DATE

[Anonymous]

Hello, i have formula below and i would like to modify them into a new tagging.. 

SSD = IF(
    'Open Orders_BOM'[SCHEDULE_SHIP_DATE] < TODAY(),
    "Backlog",
    IF('Open Orders_BOM'[SCHEDULE_SHIP_DATE] < TODAY() + 2,
    "Today",

    IF('Open Orders_BOM'[SCHEDULE_SHIP_DATE]< TODAY() + 6,
    "N5D",
    "Future"
)))

My desired output:

Logically:
N5D will cover all "Backlog" , "N5D", "Today"
Today will cover "Backlog","Today"
Future will be "N2W"

Right now, the data is 

So for the DESIRED OUTPUT:

SSD	Component Code
Backlog	4180
Today	2326
Future	36740

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You can create a new table as follows, this table does not create any relationship with the main table.

In my main table, today in your formula I took the fixed date October 20, 2021.

Now create the measure as

Component Code = 
SWITCH (
    MAX ( 'Table'[SSD] ),
    "Backlog",
        CALCULATE (
            SUM ( 'Open Orders_BOM'[Component Code] ),
            FILTER ( 'Open Orders_BOM', [SSD] IN { "Backlog", "N5D", "Today" } )
        ),
    "Today",
        CALCULATE (
            SUM ( 'Open Orders_BOM'[Component Code] ),
            FILTER ( 'Open Orders_BOM', [SSD] IN { "Backlog", "Today" } )
        ),
    "Future",
        CALCULATE (
            SUM ( 'Open Orders_BOM'[Component Code] ),
            FILTER ( 'Open Orders_BOM', [SSD] = "Future" )
        )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-stephen-msft you are a genius. thank you!

[selimovd]

Hey @Anonymous ,

do you want to add a new measure or a new calculated column?

The code you posted would only work with a calculated column.

Where exactly are you struggling or what is your question?

Best regards

Denis

[Anonymous]

@selimovd i would like to add it as a calculated column if that is the only way it works.

So for my DESIRED OUTPUT:

SSD	Component Code
Backlog	4180
Today	2326
Future	36740

[selimovd]

@Anonymous if you want a new column that in categorizing into Backlog, Today, N5D and Future depending on the value of the column 'Open Orders_BOM'[SCHEDULE_SHIP_DATE], then that's the way to go.

Then my question is where are you struggling?