Forum Discussion
Measure with an if statment?
- 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.
- 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))
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.
If I am not mistaken this is not a measure, but would be a calculated column, correct?