Date Relationship Problem

[everyrichrole]

I have a problem with something that seems like it should be very simple. I have a Dates table initially generated by AUTOCALENDAR() and also a Consumables table with ID and Dates Used columns. When I try to relate the Date Used in the Consumables table to the Date field in the Dates table, I am unable to get a count by date in the report view. See pictures.

[RMDNA]

Hi @everyrichrole,

It would be helpful to have a picture of your data, but my first steps would be to set both of your date fields to the Date or DateTime type, and make sure they're in the same format (e.g. both DD/MM/YYYY, etc.).

[everyrichrole]

Thanks for your reply, @RMDNA. Those are the first things I tried. Here's a couple pics of the data.

[RMDNA]

Hmm, your dates don't look out of alignment. Could you share your PBIX via dropbox/onedrive/etc.?

[everyrichrole · Accepted solution]

Unfortunately, I can't release the PBIX without also giving out a bit too much information about our data sources. I can tell you that I tried creating a new calculated column in the Consumables table with =DATEVALUE(Date Used), and I was able to get the relationship working by using that column. So I suspect this may be an internal bug with the date type/format casting of the Date Used column.

[Portneo]

Thank you! That solved my Problem too! 🙂

[Anonymous]

Thanks a lot, this solution worked for me as well, Before using this solution i wasted almost 5/7 hours but not able to reach the desired results with RELATED function output, finally got this solution and it worked 🙂

[meorir]

I had the same problem, DATEVALUE solution works nicely, what worked for me also was changing data type during import from date/time to date only.

Maybe someone will find this useful.

Best regards

[RMDNA]

Glad to hear it's fixed! Go ahead and mark your own comment as the solution.