Forum Discussion

CBrussee's avatar
CBrussee
New Member
2 years ago

DAX expression with Date + Days

I’m looking to create a pie chart that shows my dates in 3 categories (High, Med, Low).   I’m using a calculated column to create the chart, with this DAX expression but getting an error about comparing value types. 

So if my date is greater than today+365 days, then it is Low.  If the date is greater than today but less than today+364 days, then Med.  And if my date is Less than today, than High.

 

I’m not sure how to fix it.  Any ideas?

 

RAG = SWITCH (

TRUE(),

(‘Table Name'[Table Column] > TODAY() + 365), "LOW",

(‘Table Name'[Table Column] > TODAY()) AND (‘Table Name'[Table Column] < TODAY() + 364 ), "MED",

‘Table Name'[Table Column] <= TODAY(), “HIGH"

)

Need Help

2 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi CBrussee 

    It seems that your opening and closing parentheses are in the wrong places and that is also a wrong use of AND.

    AND is supposed to be used as AND (condition1, condition2). The fourth row should have been written as below

     

    AND( 'Table Name'[Table Column] > TODAY (), 'Table Name'[Table Column] < ( TODAY () + 364 ) ), "MED"

     

    or you could use && instead for a logical and (a single & is for concatenating).

     

    Try this formula below:

     

    RAG =
SWITCH (
    TRUE (),
    'Table Name'[Table Column]
        > ( TODAY () + 365 ), "LOW",
    'Table Name'[Table Column] > TODAY ()
        && 'Table Name'[Table Column]
            < ( TODAY () + 364 ), "MED",
    'Table Name'[Table Column] <= TODAY (), "HIGH"
)

     

    Also make sure that the data type of the column in question is actually a  date.

  • CBrussee's avatar
    CBrussee
    New Member
    2 years ago

     

    Thanks danextian - I corrected my AND and also ended up with something like this to help correct the errors - 

    COLUMN = SWITCH (

    TRUE(),

    (VALUE('Table'[Column-Name]) > TODAY() +365), "GREEN",

    AND((VALUE(''Table'[Column-Name]) > TODAY()), VALUE(''Table'[Column-Name]) < TODAY() + 364 ), "AMBER",

    VALUE('Table'[Column-Name]) <= TODAY(), "RED" )