Date table troubleshooting help

[Anonymous]

Hi - I've got a weird issue that I can't figure out.  

- I created a master date table from DAX code.  Two fields in it are month number & day of month.

- I have a relationship between the standard date table "Date" field and my data table "Target Assessment Date" field (Relationship of date table with data table picture).

- when I display a field from my data table "Target Assessment Date" in a Matrix Visual with the month number as a column and day of month as a row, I get (4) values that don't have a month number or day of month  (Matrix Visual picture).

- when I "See Records" that don't have these values, I see four rows but they all have "Target Assessment Dates" (See Records for 4 without month or day picture).

- how could (4) records be blank for month number or day of month?

Thanks for any help.

 

Matrix Visual

 See Records for (4) without month or dayRelationship of date table with data table

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

As tested, if both date column in two tables are in the correct date type, it should be correct when adding them in the matrix as you did.

If i add some space before one date value in a cell, then it shows error visual as yours.

date value should be "9/7/2019", but i change it to "   9/7/2019"

To view what format the data value is, you can click on the "1" above, then select "see records",then on the next page, click on the date and right-click on it and select "show data". 

If possible, find the valid cell from your data source and replace with the proper one.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks so much! That was it.  I should listen to everyone and ALWAYS clean my data.

[TomMartens]

Hey,

I'm wondering of both date columns have the same date format e.g meaning something like this: YYYY-MM-DD

Regards,
Tom