topic Re: if date possible switch in DAX Commands and Tips

if date possible switch

matts29 — Thu, 08 Feb 2024 14:47:13 GMT

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 years ago then 1

rdate= should be re-ordered within 6 months then 2

rdate= order last reviewed less than 2 and a half years ago then 3

Thank you

Re: if date possible switch

govindarajan_d — Thu, 08 Feb 2024 15:17:56 GMT

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

Re: if date possible switch

matts29 — Thu, 08 Feb 2024 15:53:34 GMT

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

Re: if date possible switch

govindarajan_d — Thu, 08 Feb 2024 15:58:17 GMT

Hi matts29,

Can you show the formula and some sample data?

Re: if date possible switch

matts29 — Thu, 08 Feb 2024 16:07:07 GMT

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

Re: if date possible switch

govindarajan_d — Thu, 08 Feb 2024 17:07:54 GMT

Hi matts29 ,

Are you using the formula in Calculated column or measure?

Re: if date possible switch

matts29 — Thu, 08 Feb 2024 17:10:07 GMT

In a calculated column?

Re: if date possible switch

govindarajan_d — Thu, 08 Feb 2024 18:37:09 GMT

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

Re: if date possible switch

matts29 — Fri, 09 Feb 2024 09:53:03 GMT

Ahh that is fantastic-thank you! I need to learn now what everything is doing 🙂