Duplicate Dates in Date column

[yush_amateur]

Hi !
My original Retail_Transactions[Date] column contains duplicate dates, and hence I created another Calendar table (Because when I created columns with startofmonth, it threw an error that my Retail_Transactions[Date] contains duplicate dates) with

Calendar =

CALENDAR(

    MIN(

        Retail_Transactions[Date]

    ),

    MAX(

        Retail_Transactions[Date]

    )

)

I joined these two tables in the model, but now when I'm creating line charts using this Calendar[Date] field, it doesn't show any result. If I use the field from Retail_Transactions[Date], it shows wrong results. When I turn off the relationship, and use Retail_Transactions[Date] field, it shows the correct result.

Can you tell me why I'm not able to use Calendar[Date] ?

Attaching screenshot for better clarity.

[Ritaf1983 · Accepted solution]

Hi @yush_amateur 
According to the lack of the calendar icon on your picture i see that "date" in the fact table is not date data type :

Please change it with PQ 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Raveeshlokanath]

I have the same issued, but data type is already set to Date, but it still shows duplaciates and Calendar Icon not appearing at all.

[Ritaf1983]

Hi @Raveeshlokanath 

Please share link to the pbix and i 'll try to help.

[Raveeshlokanath]

Hi @Ritaf1983  I can't share the pbi as it contains sensitive data..   please check the Pics above and if you suggest on nex steps will be helpful. Thanks in advance.

[Raveeshlokanath]

hI @Ritaf1983  It is Direct query.

[Ritaf1983]

Hi @Raveeshlokanath 

First, I see that your data type is date/time rather than Date.

So, I would first try changing it to Date.

Unfortunately, I don't have any experience with direct query.

So, if the changes I suggested don't work, I recommend simply writing your question as a well-organized post with all the details.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[yush_amateur]

Hi @Ritaf1983 ,

Thank you for the solution. I had checked that in the PQ, and it correctly showed Date/Time, but this time I changed it regardless to Date, and then it worked.   
Thank you.

[Ritaf1983]

Happy to help 🙂