Forum Discussion

NBOnecall's avatar
NBOnecall
Icon for Helper V rankHelper V
7 years ago
Solved

Measure with an if statment?

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 t...
  • Cmcmahan's avatar
    7 years ago

    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's avatar
    NBOnecall
    7 years ago

    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))