Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

IF ELSE for DATE

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:

 

SSDComponent Code
Backlog4180
Today2326
Future 36740

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

     

5 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

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

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

       

      So for my DESIRED OUTPUT:

       

      SSDComponent Code
      Backlog4180
      Today2326
      Future 36740

       

      • selimovd's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

      Anonymous you are a genius. thank you!