Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NBOnecall
Helper V
Helper V

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

Out Of Stock Helop.PNG

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!

2 ACCEPTED SOLUTIONS
Cmcmahan
Resident Rockstar
Resident Rockstar

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.

View solution in original post

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

View solution in original post

5 REPLIES 5
Cmcmahan
Resident Rockstar
Resident Rockstar

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.

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

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
Resolver III
Resolver III

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.