New condition to existing measure

[ShazzaGreen]

Hi All, I'm sure this will be a simple solution for those in the know 🙂

I have an exisiting measure with a series of conditions to determine the result. Basically it's looking to determine if we delivered on time or not. I have just been asked to update the reporting to reflect that the delivery time target has now been changed from 3 days to 2. Would have been simple enough except they only want the new 2 day time frame to apply for jobs that are assigned after 1/08/21. 

Can anyone suggest ammendments to this measure so that jobs with AB_Assign Date BEFORE 1/8/21 require AB_TAT <=3 for a YES, and jobs with AB_Assign Date AFTER 1/8/21 require AB_TAT <=2 for a YES?

Current measure

AB_TAT Achieved = IF('BDoD Program'[AB_TAT]=BLANK(),"",IF('BDoD Program'[AB_TAT]<=3,"Yes",IF('BDoD Program'[AB_Assign Date]=0,"Yes","No")))

[v-luwang-msft]

Hi @ShazzaGreen ,

Try the below dax:

AB_TAT Achieved =
IF (
    'BDoD Program'[AB_TAT] = BLANK (),
    "",
    IF (
        ( 'BDoD Program'[AB_TAT] <= 3
            && 'BDoD Program'[AB_Assign Date] <= "2021/8/1" )
            || ( 'BDoD Program'[AB_TAT] < 2
            && 'BDoD Program'[AB_Assign Date] > "2021/8/1" ),
        "Yes",
        IF ( 'BDoD Program'[AB_Assign Date] = 0, "Yes", "No" )
    )
)

Wish it is helpful for you!

Best Regards

Lucien

[v-luwang-msft]

Hi @ShazzaGreen ，

Check if the field AB_TAT type is time, if yes：

AB_TAT Achieved =
IF (
    'BDoD Program'[AB_TAT] = BLANK (),
    "",
    IF (
        ( 'BDoD Program'[AB_TAT] <= 3
            && 'BDoD Program'[AB_Assign Date] <=date(2021,8,1) )
            || ( 'BDoD Program'[AB_TAT] < 2
            && 'BDoD Program'[AB_Assign Date] >date(2021,8,1)),
        "Yes",
        IF ( 'BDoD Program'[AB_Assign Date] = 0, "Yes", "No" )
    )
)

if not ,change the  AB_TAT  to date type:

Best Regards

Lucien

[ShazzaGreen]

That resolves the error. However, I don't think the formula is working (either that or the change to the format is causing a problem). The AB_TAT field is now displaying as dates instead of number of days. If I filter on Wednesday, 3 January 1900 in an attempt to filter on TAT of 3 days, all results are showing as 'No' for TAT achieved regardless of the assign date being either before or after August.

[v-luwang-msft]

Hi  @ShazzaGreen ,

I read that your dax is not consistent with the dax I provided above, you can try the dax I provided to see if it works, also is it convenient to provide your pbix file for me to test and adjust，and remember to remove confidential data.

Best Regards

Lucien

[ShazzaGreen]

Thanks for the suggestion Lucien. Sorry for the delay responding, I had to take some unexpected leave after posting this.

Unfortunately I'm getting this error when applying the formula;

"DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

[Ashish_Mathur]

Hi,

Does this work?

AB_TAT Achieved = IF('BDoD Program'[AB_TAT]=BLANK(),BLANK(),IF(AND('BDoD Program'[AB_TAT]<=3,'BDoD Program'[AB_Assign Date]<DATE(2021,8,1)),"Yes",IF(AND('BDoD Program'[AB_TAT]<=2,'BDoD Program'[AB_Assign Date]>DATE(2021,8,1)),"Yes","No"),"No")))

[ShazzaGreen]

Thanks for the suggestion Ashish. Sorry for the delay responding, I had to take some unexpected leave after posting this.

Unfortunately I'm getting this error when applying the formula;

"DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

[Ashish_Mathur]

Hi,

The entries in column AB_TAT are most probably text entries.  In the Query Editor, please change the data tpe of this column as Date.

[ShazzaGreen]

That resolves the type error. Now I'm getting this error.

"The syntax for ')' is incorrect. (DAX(IF('BDoD Program'[AB_TAT]=BLANK(),BLANK(),IF(AND('BDoD Program'[AB_TAT]<=3,'BDoD Program'[AB_Assign Date]<DATE(2021,8,1)),"Yes",IF(AND('BDoD Program'[AB_TAT]<=2,'BDoD Program'[AB_Assign Date]>DATE(2021,8,1)),"Yes","No"),"No")))))"

[v-luwang-msft · Accepted solution]

Hi @ShazzaGreen ,

According your dax ,try the below:

dax =
IF (
    'BDoD Program'[AB_TAT] = BLANK (),
    BLANK (),
    IF (
        AND (
            'BDoD Program'[AB_TAT] <= 3,
            'BDoD Program'[AB_Assign Date] < DATE ( 2021, 8, 1 )
        ),
        "Yes",
        IF (
            AND (
                'BDoD Program'[AB_TAT] <= 2,
                'BDoD Program'[AB_Assign Date] > DATE ( 2021, 8, 1 )
            ),
            "Yes",
            "No"
        )
    )
)

Best Regards

Lucien

[ShazzaGreen]

That did it! Thank you so much for your help 🙂 

[Ashish_Mathur]

Just check the brackets.  There are some extra ones.  Remove them.  DIY