Assign Value to Calculation in DATEDIFF and Use Case Handling

[irnm8dn]

I am currently using the following DAX statement to calculate the difference between two dates.

Day Count of Drop = SWITCH(
TRUE(),
'KPI Dashboard AD'[Campaign Creation Date]<'KPI Dashboard AD'[Start Date], DATEDIFF('KPI Dashboard AD'[Campaign Creation Date],'KPI Dashboard AD'[Start Date],DAY),
'KPI Dashboard AD'[Start Date]>'KPI Dashboard AD'[Campaign Creation Date], DATEDIFF('KPI Dashboard AD'[Start Date],[Days Between Drop Creation and Start],DAY)*-1,
0
)

This seems to work, but there are two use cases that this statement doesn't account for:

1. When Start Date is greater than the Creation Date - I would like this to show "Error"
2. When Start Date is the Same as Creation Date - I would like this to show "0"

Once the above use cases have been handled, I am looking to have a process that assigns the value to the calculation.

When "Error" show "Error" (Use Case #1 above)

When calc is "0" Very Urgent (Use Case #2 above)

When calc between "1" and "3" Urgent

When calc between "4" and "7" Standard
When calc is "8" or greater, Not Urgent

Appreciate the feedback on how to do this.

[Phil_Seamark]

HI @irnm8dn

Is this calculated column close?

Day Count of Drop = 
VAR MyDateDiff = INT('KPI Dashboard AD'[Start Date]) - INT('KPI Dashboard AD'[Campaign Creation Date])

RETURN SWITCH(
                True(),
                MyDateDiff < 0 , "Error",
                MyDateDiff = 0 , "Very Urgent" ,
                MyDateDiff < 4 , "Urgent" ,
                MyDateDiff < 8 , "Standard" ,
                "Not Urgent"
                )

[irnm8dn]

@Phil_Seamark

Phil, this is awesome.

Can you help adjust my original statement so that the following works as a proper calc?

When Start Date = Create Date "0"
When Start Date is greater than Create Date = "Error"

Essentially, I will have tow columns in my table

1.  The count of days between

2.  The  value which you addressed in your original post

Thanks!

[Phil_Seamark]

HI @irnm8dn

So just to clarify, you are after a column that shows the difference between your two dates in days - but if the value is negative you want to display "Error"?

That would mean mixing data types, so all your numbers would be text.  

Have I understood correctly, or perhaps you could post a mock up (using excel) of what you would like the column to look like.

I'm just not sure what you mean by "proper calc"

[irnm8dn]

Thanks for the clarification Phil.  You understand it correctly, and I see the conflict in what I have asked for.

I would take the negative value with a calc.  My statement does not address this condition.

That way I could differentiate between negative and true "0".

Sceondly, if you could help me understand your initial solution (which works great), that would help this newbie to not ask again.  Teach a man to fish, if you know what I mean.

Thanks again.