Date Table not working with relationship

[ComputerHabit]

I'm learning how to use PowerBi.  I'm trying to use a date table.  I have a sharepoint list that has items.  I've setup a relationship between the date table and the Created column in the Sharepoint list.  The relationship doesn't seem to work.  I'm not sure what I'm doing wrong.  Articles show how to create a date table but ultimately fail when showing how to use the thing.

I created a table like this:

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

I setup a relationship like this.

When I add columns to my report the data doesn't show.  

I've tried refreshing data, recreating the relationship, changing the datatype and changing the format. 

As mentioned I'm new.  Maybe theres a special save button I didn't notice.

[amitchandak]

Check if the date coming from SharePoint is having timestamp in it. And it is not visible because of the format. And that is casing join to fail.

[ComputerHabit]

After making changes do I need to hit a button to see the data?

[ComputerHabit]

Nothing.  No luck.  I have tried various formats and changing the field to date and date\time back and forth.  Nothing happens.  Is there a debug button or anything?

[PaulDBrown]

@ComputerHabit 

Is the relationship linking your Date field from the Date table to the "Created" field in your "Events" table?

[ComputerHabit]

Yes.  I'm linking the Date field to the Created field.

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[ComputerHabit]

Dates appear to be the same.

[ComputerHabit]

The query for Sharepoint list has the time stamp.  I thought by changing the data type and so on this would work.  Do I need to change it in the query instead?  I thought that would create to much load time.

[vanessafvg]

I would convert it as its obviously not working.   create a new column only with date values.   ie duplicate that column, and then from the transform menu in power query change it to date / then date only to make sure

something is obviously not matching up, make sure they are both only dates not datetime in datatype too

[ComputerHabit]

I'll retry after lunch.  Ugh...

It's odd that a single date is showing.  Is everyone sure there isn't a secret button?

[vanessafvg]

@ComputerHabit  one step closer right?   are you sure you have all the dates?  compare the tables side by side, remove the relationship

[ComputerHabit · Accepted solution]

There appears to be two different places to format data.

When using Get Data and bringing in the initial SharePoint list the Date columns come as Any\Text.  I had initially left it like that and made my Date table and joined them.  I initally tried changing the data type in the relationship area.  That didn't have the desired effect.  Only after going back to the Sharepoint list data and modifying the CreatedBy column from Text to Date did it start working.

[vanessafvg]

what is the date type for the created column?  have you got it as date time?

[vanessafvg]

@ComputerHabit   look at the display format in the visual pane under modelling for the sharepoint date, have you previewed it in the query pane?  (ie edit queries)