Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

conditional values according to the delay with today

Hi everybody!

 

I'm trying to get a visual highlighting delays like 1 week, 2 weeks, 3 weeks, 1 month, 2 months, 3 months and more than 6 months.

 

To calculate the delay, I get a column entitled Response Due Date.

 

The aim is that, according to the gap between TODAY() and the date available in the column Response Due Date, the appropriate value (see below) will be displayed.

 

For the moment, I only get the measure Delay = INT(TODAY()-('mytable'[Response Due Date].[Date])) that gives me the gap with a number of days. Now, I have to get this kind of result if between 0 and 7 I want to see 1 week elseif between 8 and 14 I want to see 2 weeks, etc.

 

I'm not sure it needs to be done with a measure, maybe a custom column in the query editor but waybe I'm wrong.

 

Thanks for your attention.

 

Regards,

CR

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi,

    I finally found a way to solve it so let me explain how I proceeded.

    I just added a calculated column with this specific code:

     

    Column = IF('mytable'[Delay]<0;"on time";
            IF(AND('mytable'[Delay]>0;'mytable'[Delay]<=7);"1 week";
                IF(AND('mytable'[Delay]>7;'mytable'[Delay]<=14);"2 weeks";
                    IF(AND('mytable'[Delay]>14;'mytable'[Delay]<=21);"3 weeks";
                        IF(AND('mytable'[Delay]>21;'mytable'[Delay]<=28);"4 weeks";
                            IF(AND('mytable'[Delay]>28;'mytable'[Delay]<=90);"1 month";
                            IF('mytable'[Delay]>90;"more than 3 months";"TO CHECK")
                    ))))))

    Well, first a measure to calculate the number of days between 2 dates, then a calculated column based on this measure.

     

    I like this application !!!!!!!!!!!!!!!

     

    CR

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi,

    I finally found a way to solve it so let me explain how I proceeded.

    I just added a calculated column with this specific code:

     

    Column = IF('mytable'[Delay]<0;"on time";
            IF(AND('mytable'[Delay]>0;'mytable'[Delay]<=7);"1 week";
                IF(AND('mytable'[Delay]>7;'mytable'[Delay]<=14);"2 weeks";
                    IF(AND('mytable'[Delay]>14;'mytable'[Delay]<=21);"3 weeks";
                        IF(AND('mytable'[Delay]>21;'mytable'[Delay]<=28);"4 weeks";
                            IF(AND('mytable'[Delay]>28;'mytable'[Delay]<=90);"1 month";
                            IF('mytable'[Delay]>90;"more than 3 months";"TO CHECK")
                    ))))))

    Well, first a measure to calculate the number of days between 2 dates, then a calculated column based on this measure.

     

    I like this application !!!!!!!!!!!!!!!

     

    CR