SharePoint Date Issue

[TrentS]

While creating a column to use as a conditional formating field, I have encountered an issue we cannot explain.

I backed off the conditional set when I was verifying my VAR steps to just look at the date conversion. (We ultimately want a date to show a red background in a visual when a contract is within 3 months of expiring.)

The DAX is simply:

endminus3 = IF(ISBLANK('Table'[Contract End Date]),blank(),DATEADD('Table'[Contract End Date],-3,MONTH))

If the contract end date is not blank, give me a date 3 months prior to it essentially.

The data source here is SharePoint List.

The issue is that the column populates correctly for roughly 2/3 of the rows.

The other rows come in as a blank even though there is a valid date and the correct column format (Date/Short Date for both columns).
Anyone seen this behavior before?
We have been searching for any commonality among the missing data fields and have not been able to find any. In any case, since the custom column only references a single data field, no other fields should have an impact.

We have gone into one of the contracts on SharePoint and manually edited the date to verify no additional characters and correct format. No change in Power BI after a refresh.

What are we missing?

Thanks,
Trent

[jgeddes · Accepted solution]

I created a small sample of dates to try an re-create the issue. And I was successful in getting some blanks and some correct values. I changed the endminus3 measure to return the date 12/31/9999 when blank to troubleshoot if it was a "blanks" issue or a date issue. The blanks were not the problem, so it is something with the dates. I looked at the dateadd function documentation and found

• The result table includes only dates that exist in the dates column.

That describe what I was seeing in my small data set. I wonder if it applies to your situation.

To get around this I added a column in Power Query to do the math there and it works just fine.

= Table.AddColumn(#"PreviousStep", "endminus3", each Date.AddMonths([Date], -3))

is what I added in Power Query

[TrentS]

You hit it spot on!
That was the limiting issue. I need to remember that one for the future.

Kudos and Solution Acceptance given.

Thanks again,

Trent

[TrentS]

@jgeddes 

Thanks for taking a look. Since it has the reference date in the 'Contract End' column, I would not think it an issue. I will go back and see if that is indeed the case.
I do have a date table which does NOT have a direct relationship to this table but is linked through another table. I had tried creating an inactive direct relationship and adding USERELATIONSHIP to the DAX but it did not seem to make any difference.
I will update on the finding from your suggestion.

Trent