Measure with an if statment?

[NBOnecall]

Hi,

I have a table, lets called "ns InventorySnapshot" and it has two columns in it, InventoryDate and IgnoreDate. I have the below measure that I was using until we brought IgnoreDate into the table.

DaysOutofStockEND = var lastdt = Calculate(max('ns InventorySnapshot'[InventoryDate]))
Return
Datediff(lastdt,TODAY(), DAY)

I now want to use this measure with the IgnoreDate. Here is an example of my table and the results I would want to see.

If IgnoreDate is blank I want to subtract todays date Max InventoryDate, but if it is not blank I want to do possibly one of two things. If IgnoreDate is less than or equal to InventoryDate then the measure would result in 0 or if IgnoreDate is larger to InventoryDate I want to subtract the data difference and use that as the result.

Hopefully I explained this well enough.

Thanks!

[Cmcmahan · Accepted solution]

Sure, this is fairly straightforward.  You want a DATEDIFF between [Inventory Date] and [Ignore Date].  In the case that [Ignore Date] is blank, you want to use today's date instead.  In the case that [Inventory Date] is after [Ignore Date] you want 0.

DaysOutofStockWithIgnore =  MAX ( DATEDIFF(MAX('ns InventorySnapshot'[InventoryDate]), 
                                     IF(MAX('ns InventorySnapshot'[IgnoreDate]), MAX('ns InventorySnapshot'[IgnoreDate]), TODAY())
                            ),0)

This should work (maybe with minor syntax issues. I'm not sure if you need to use MAX to get a single IgnoreDate or if you should use SELECTEDVALUE).  What I'm doing (from inside to out) is checking if [IgnoreDate] has a value.  If it has a value, use that, if not use TODAY().  Find the difference between the InventoryDate and the IgnoreDate/Today. Finally, it returns whichever value is larger: The difference between the dates, or 0.  So instead of a negative number, you see a 0.

Hopefully what I did makes sense.  If it doesn't, please ask questions.

[Cmcmahan · Accepted solution]

Sure, this is fairly straightforward.  You want a DATEDIFF between [Inventory Date] and [Ignore Date].  In the case that [Ignore Date] is blank, you want to use today's date instead.  In the case that [Inventory Date] is after [Ignore Date] you want 0.

DaysOutofStockWithIgnore =  MAX ( DATEDIFF(MAX('ns InventorySnapshot'[InventoryDate]), 
                                     IF(MAX('ns InventorySnapshot'[IgnoreDate]), MAX('ns InventorySnapshot'[IgnoreDate]), TODAY())
                            ),0)

This should work (maybe with minor syntax issues. I'm not sure if you need to use MAX to get a single IgnoreDate or if you should use SELECTEDVALUE).  What I'm doing (from inside to out) is checking if [IgnoreDate] has a value.  If it has a value, use that, if not use TODAY().  Find the difference between the InventoryDate and the IgnoreDate/Today. Finally, it returns whichever value is larger: The difference between the dates, or 0.  So instead of a negative number, you see a 0.

Hopefully what I did makes sense.  If it doesn't, please ask questions.

[NBOnecall · Accepted solution]

Am I lost or is your if statement not complete?

 IF(MAX('ns InventorySnapshot'[IgnoreDate]), MAX('ns InventorySnapshot'[IgnoreDate]), TODAY())

What is it comparing against?

This is what I am using.

DaysOutofStockWithIgnore = CALCULATE(MAX(DATEDIFF(Max('ns InventorySnapshot'[InventoryDate]),if(max('ns InventorySnapshot'[OC_NSL_SPECIAL]) = BLANK(), TODAY(),Max('ns InventorySnapshot'[OC_NSL_SPECIAL])),DAY),0))

[Cmcmahan]

Ah, sorry. That's the programmer in me leaking out.

It's a very common use case when programming to want to detect if a variable has a value.  Some languages, including DAX, have created a shorthand way of checking non-boolean values. A value evaluates to false if it is 0, null (BLANK() in DAX), or FALSE().  A value evaluates to true if it is not one of the above. 

As a result, these IF statements are identical:

Example1 = IF(Table[ColA] = BLANK(), "Yay!", "Boo!")

Example2 = IF(Table[ColA], "Yay!", "Boo!")

Example3 = IF(ISBLANK(Table[ColA]), "Yay!", "Boo!")

Since dates can't have a value of 0, then the only way they evaluate as false is if there is no date there.  I used that feature to quickly write the equation, instead of writing out the entire ISBLANK() check explicitly.

In addition, there are some strings that are automatically evaluated as equivalent to FALSE(). These expressions always return "Boo!":

AlwaysFalse1 = IF(FALSE(), "Yay!", "Boo!")

AlwaysFalse2 = IF(BLANK(), "Yay!", "Boo!")

AlwaysFalse3 = IF(0, "Yay!", "Boo!")

AlwaysFalse4 = IF("0", "Yay!", "Boo!")

AlwaysFalse5 = IF("", "Yay!", "Boo!")

AlwaysFalse6 = IF("false", "Yay!", "Boo!")

AlwaysFalse7 = IF(1=2, "Yay!", "Boo!")

You can learn more about it here: https://www.sqlbi.com/articles/blank-handling-in-dax/

If this is not working for you, I'm more than happy to assist further.

[ZunzunUOC]

Hi @NBOnecall , try with this:

result = IF(ISBLANK(inventorydate[IgnoreDate]);DATEDIFF(inventorydate[InventoryDate];TODAY();DAY);IF(inventorydate[IgnoreDate]>inventorydate[InventoryDate];DATEDIFF(inventorydate[InventoryDate];inventorydate[IgnoreDate];DAY);0))

Remenber change ";" per "," and use your names.

Best Regards,
Miguel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NBOnecall]

If I am not mistaken this is not a measure, but would be a calculated column, correct?