Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to add text condition in Switch

dd

 

Hello - I need to add another condition to this statement, but not sure how.
 
I need to add: allopps-products[status]="open" as one of the conditions. But it won't let me.
 
What do I need to do to be able to add this condition?
 
SWITCH(
    TRUE(),
    MAX('AllOpps-Products'[Prob.]) = 80, "30 Days",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 90, "Older than 90",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 60, "Older than 60",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 30, "Older than 30",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 15, "30 Days",
    FALSE
)
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    az38  I got it to work using this: 

     

    TRUE(),
        DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) > -1 &&  DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) > -20, "Passed already"

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , not very clear where you want to use that. But check a couple of options

    if(max(allopps-products[status])="open",
SWITCH(
    TRUE(),
    MAX('AllOpps-Products'[Prob.]) = 80, "30 Days",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 90, "Older than 90",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 60, "Older than 60",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 30, "Older than 30",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 15, "30 Days",
    FALSE
),blank())


SWITCH(
    TRUE(),
    MAX('AllOpps-Products'[Prob.]) = 80 && max(allopps-products[status])="open", "30 Days",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 90, "Older than 90",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 60, "Older than 60",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 30, "Older than 30",
    DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 15, "30 Days",
    FALSE
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak  (or anyone that can answer)

       

      The solution from Amit is great, with one exception.   

       

      How to deal with a date that is in the past?    Example: 

       

      The estimated close date may indeed be 15 days or more from today's date....but it could be 15 days in the past from today  (not 15 days FROM today).    For those estimated close dates that have passed already (from today's current date), how to address in the Switch statement?

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

        smth like

        DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 0, "0 Days",
DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> -15, "Passed already",
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Anonymous 
     
    You can add  allopps-products[status]="open" using  && notation
     
     
    SWITCH(
        TRUE(),
        MAX('AllOpps-Products'[Prob.])  = 80 && allopps-products[status]="open" , "30 Days",
        DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 90, "Older than 90",
        DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 60, "Older than 60",
        DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 30, "Older than 30",
        DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY)> 15, "30 Days",
        
    )
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)