Forum Discussion

matts29's avatar
matts29
Frequent Visitor
2 years ago
Solved

if date possible switch

Hi    If someone can assist please?   I would like to say in 1 column the below please? The rdate contains a series of dates in the past and the future.   rdate = last order over 2 and a half ...
  • govindarajan_d's avatar
    govindarajan_d
    2 years ago

    Ok. I wrote the formula for a measure actually. 

    Please use the following for calculated column:

    rdateCalc =
VAR __Difference =
    DATEDIFF ( 'SomeDateTable'[rdate], TODAY (), DAY )
VAR __Result =
    IF (
        __Difference > 913,
        1,
        IF (
            __Difference < 913
                && __Difference > 0,
            3,
            IF ( __Difference < 0 && __Difference > -180, 2, BLANK () )
        )
    )
RETURN
    __Result

     

govindarajan_d's avatar
govindarajan_d
Icon for Super User rankSuper User
2 years ago

Hi matts29 ,

Can you try this?

rdateCalc =
VAR __Difference =
    DATEDIFF ( MAXX ( 'SomeDateTable', 'SomeDateTable'[rdate] ), TODAY (), DAY )
VAR __Result =
    IF (
        __Difference > 913,
        1,
        IF (
            __Difference < 913
                && __Difference > 0,
            3,
            IF ( __Difference < 0 && __Difference > -180, 2, BLANK () )
        )
    )
RETURN
    __Result

 

 

  • matts29's avatar
    matts29
    Frequent Visitor
    2 years ago

    Thanks but I just have all blanks in the column unfortunatley...

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

      Hi matts29,

       

      Can you show the formula and some sample data?

      • matts29's avatar
        matts29
        Frequent Visitor
        2 years ago

        sure this is the adjusted formula

        VAR __Difference =
    DATEDIFF ( MAXX ( 'SomeDateTable', 'SomeDateTable'[rdate] ), TODAY (), DAY )
VAR __Result =
    IF (
        __Difference > 913,
        1,
        IF (
            __Difference < 913
                && __Difference > 0,
            3,
            IF ( __Difference < 0 && __Difference > -180, 2, BLANK () )
        )
    )
RETURN
    __Result

         

        This s the sample data from the date table