Forum Discussion
Cannot match date to date table
I'm trying to make reports from our ticketing system. Most require displaying ticket count per month with some added info or filtering. I created a calendarauto table with relation to the ticket table in DB (directquery) and added a relationship. Problem is when using these together, the tickets' timestamps are matches with the whole date and time to rows in calendar so at the moment I get only a single match for a ticket that happens to have time 12:00:00 AM. I tried changing data types for the fields from datetime to date but that only changed displayed values. I have a separate GroupBy table that works to some extent but it breaks context and relations and probably something else I don't even know.
Can I somehow enter custom aggregation or compare rules?
relationshipscreated visualization. Left is count of ticket Ids, calendar date, calendar month year part and ticket creatime_time., middle is raw calendar, right is raw ticket create_time
Hello AlmostDev
Did you need to use the hours?
If not, go to power query and change to date the two columns.
close and applly
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC
4 Replies
Hello AlmostDev
Did you need to use the hours?
If not, go to power query and change to date the two columns.
close and applly
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BCI thought this wouldn't work because directquery doesn't allow data type changes but did for this? I just need the dates for this so this is fine. In order to still have times in case I need them (eg for SLA) I made a duplicate column which I changed to Date.
Some additional data type and format changes and now I have what I need
Anonymous , Create a new column
Create_date = Datevalue([Craeted_time])
join this with Date table
