Displaying a Blank for a Date Calculation

[Canknucklehead]

Hi all,

I'm trying to put together a visual that will show when product is received vs the anticipated delivery date. I've managed to set up a custom column to calculate the days difference between the two date fields (Received - Scheduled). It is calculating correctly, showing Positive values for late shipments, negative for early and 0 for on time. 

However, I need to take into account product that has a future delivery date and no current received date. Right now, I'm getting -737845 as the result if the scheduled date is Feb. 22nd and the received date is Blank. I've tried the following formula - 

Column = IF(ISBLANK([Last Received Date]),BLANK(),([Last Received Date] - [Scheduled Date])*1.)

But BI is not happy with that - just gives me an error. It unhelpfully identifies the letter "K" in Blank as the problem. I've tried removing that, adding a THEN statement, and other iterations, all to no avail. 

Suggestions, as always, are appreciated.

[PhilipTreacy]

Hi @Canknucklehead 

Download my sample PBIX file with code/data shown below

Your initial post showed DAX code used to create a Calculated Column,so I gave you that code modified to get the result you want.  But I think you tried that in Power Query hence the error you got. Power Query is case sensitive so IF will not be recognised as it expects if.

So working in Power Query I ceated some data based on your screenshot

Then adding a Custom Column and subtracting Scheduled Due Date from Last Received

= Table.AddColumn(#"Changed Type", "Days Early or late", each [Last Received to Stock] - [Scheduled Due Date])

Gives this

Have a look at my PBIX file to see what I have done to get this

I don't know why you're getting the results you are - please supply your PBIX fle so I can check it.

Regards

Phil

[Canknucklehead]

I'm unable to upload the full PBIX as it is linked to an internal DB2 database and will not function for you outside our network.

Part of the problem appears to be that instead of providing a <null> value for Last Received to Stock if the field does not contain data, it simply shows a blank field instead. How it is calculating -700000+ days is beyond my understanding.

Using your code snippets from above I've tried implementing them in Power Query and I've tried implementing them in DAX, but I get different errors. Changing "IF" to "if" in power query still gives me errors

Power Query Error:

DAX Error:

If I can get it to provide a BLANK result in the calculated column, I can then modify my visuals to show "open purchase orders" based on that information. But MS error indicators can be so vague at times, and it is frustrating. But I do appreciate your efforts in helping me. I can usually find code online that helps me, but every once in while I run into these really strange problems.

[PhilipTreacy · Accepted solution]

Hi @Canknucklehead 

DAX and M (Power Query) are different languages and you can't use the same commands in both.  The DAX code I supplied

Column = IF( OR( ISBLANK([Last Received Date]) , ([Last Received Date] - [Scheduled Date]) < 0 ), BLANK() , [Last Received Date] - [Scheduled Date] )

will not work in Power Query.

It's a completely different set of function names and syntax.

Thats why I supplied a PBIX file with some code in Power Query that does what you want using this code to create a Custom Column

= Table.AddColumn(#"Changed Type", "Days Early or late", each [Last Received to Stock] - [Scheduled Due Date])

You need to download my PBIX file and look at the code in Power Query then try to apply it to your code.  

Download PBIX file

If you can't get it to work I can't do much more without seeing your Power Query code and at least a sample of your data in Power Query.

Regards

Phil

[Canknucklehead]

I figured out the problem. What shows as blank dates in Power BI actually contain an invalid date of 0001-01-01. That's why I'm getting calculations and not simply <null> - the fields are not empty.  That's why the coding doesn't work - bad data.

Thank you for your help.

[V-lianl-msft]

Hi @Canknucklehead ,

Based on your description, you can get the correct calculation. What do you think about the blank date?

Do you want blank dates to be displayed without calculation?Unfortunately, I can't clearly understand your needs.

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

[PhilipTreacy]

@Canknucklehead 

So you are doing this in Power Query?  Your original post showed DAX code?

Please show some of your data by providing the PBIX file or copy/paste some data I can work with.

Regards

Phil

[Canknucklehead]

I'm happy to use either, as long as I can get it to work. I am still new at this, and have difficulties differentiating between DAX and Power Query code at times, so I always try solutions in both to see where they work.

If I try your code in DAX, I get errors that it can't find the fields marked. Even if I populate the Table name at the beginning, it gives errors concerning the fields.

I've tried modifying the code (and other code) in Power Query - just different errors. Below is an example of what I have working so far -  a custom column created in Power Query is calculating the information mostly correctly. It just has a problem with product that is scheduled to be delivered later this month and no last receive date yet. 

[PhilipTreacy]

Hi @Canknucklehead 

In the last term for the IF, you are multiplying by 1. ?  is that suposed to be some other value?

You can try this but it is just multiplying the last term by 1, so there is no need for that?

Column = IF( OR( ISBLANK([Last Received Date]) , ([Last Received Date] - [Scheduled Date]) < 0 ), BLANK() , [Last Received Date] - [Scheduled Date] )

Regards

Phil

[Canknucklehead]

This is the error I get with your code :

Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly.  🤔

I really hate DAX/Power Query.  😝