cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbrandone
Helper III
Helper III

Date Table and Data Table not relating properly

I have a date table, I confirmed that it is marked as the date table, but whenever I use "Month Year" or any form of the date table in my visuals in conjunction with my data tables, the visuals do not seperate by date and instead show the sum total of the entire data table spread over all dates. I verified that the date column in my data table is in proper data type, "date/time". If I do not use the date table and instead use the date column in the table I am referencing in the visual, the data comes back perfect. The issue is that I want to use "Month Year" as the axis. I know that I can create a column in the data table that will pull the Month Year the way I want to, but I really want to figure out why I cannot use the date table in conjunction with my data tables if the date table and data tables have a relationship, the relationship is active and is one to many. 

 

Has anyone had this issue or now what the culprit is?

1 ACCEPTED SOLUTION

yes, it does matter: both fields must be the same type. If your data table contains date/time, you should create separate new columns for date and time in your data table (and then create a dimension table for time to join to the data time column). The date fields in both your date table and data table must be type date 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Super User
Super User

the date field in your date table should be type date (not date/time). Both date fields in your date table and data table should incidentally be the same type





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






So the date column in the date field is set to "Date". Sorry, I mis wrote the original, the data table has a date/time designation. Does that make a difference? Can the date column of the date table be designated as "Date" and the data table date column be "date/time"? Time has to be in the data table time column for our needs. Below is the outcome I am getting sometimes as well. All of the data is coming back as blank instead of in the months they are supposed to be.

 

dbrandone_0-1625764736240.png

 

yes, it does matter: both fields must be the same type. If your data table contains date/time, you should create separate new columns for date and time in your data table (and then create a dimension table for time to join to the data time column). The date fields in both your date table and data table must be type date 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!