Forum Discussion
Displaying a Blank for a Date Calculation
Hi Anonymous
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.
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
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.
Super UserHi Anonymous
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.
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
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.