Forum Discussion
M Query Returns Error for Calculated column referencing null values of date column
I have a calculalted M query column
Receive and Close Date Flag
=
if
([Actual Closed Date] >= #"Parameter Date Closed Start Date"
or
[Date Received] >= #"Parameter Date Received Start Date" and [Deal Status Display] = "Open")
then "True" else "False"
And two Parameter of same date value is set at
Note that the result column is showing Error whe the Actual Close Date is Null
How can I fix this in my dax ?
8 Replies
- AndrewPFHelper V
Use this:
try if([Actual Closed Date] >= #"Parameter Date Closed Start Date" or [Date Received] >= #"Parameter Date Received Start Date" and [Deal Status Display] = "Open") then "True" else "False" otherwise null
see also:
- AnonymousNot applicable
- AnonymousNot applicable
This gives Token eof Expected error.
- AndrewPFHelper V
You need a "try" in front of the "if".
The syntax is:
try [THIS] otherwise [THAT]
where [THIS] is what you want to calculate i.e. your entire "IF" statement, and [THAT] is what you want to display if your calculation fails for any reason.
- AndrewPFHelper V
This is the code:
try if [Actual Date Closed] >= #"Parameter Date Closed Start Date" or [Date Received] >= #"Parameter Date Received Start Date" and [Deal Status Display] = "Open" then "True" else "False" otherwise null
It was Actual Date Closed, not Actual Closed Date, and there were some erroneous brackets
- AnonymousNot applicable
It is not working for me. Can you please update or test it out in the PBIX.
As an FYI, the cells showing as Error should display as False or True.
- AndrewPFHelper V
If I type in the code exactly as described in my previous post, including the "try" at the beginning, and without the erroneous brackets, it works:
- AndrewPFHelper V
Please mark the solution as "Accepted" if this works for you. Thanks.