Calendar multiple relations in one table not working

[Anonymous]

Hello All,

I have created a calendar table and have multiple date relationships with a single table. The table has an assigned date and a closed date for work orders. I want to show how many workorders are assigned and closed per month and per technician. To do this I created a column that had the number of work orders per ticket (1 order per ticket) and then created a measure called Total_WO (standing for total work orders). I tried to create a Total_WO_Assigned measure and a Total_WO_Closed measure using the following code:

Total_WO_Assigned = CALCULATE([Total_WO], USERELATIONSHIP('Calendar'[Date],tblWork_Orders[WO_AssignDate]))

Total_WO_Closed = CALCULATE([Total_WO],USERELATIONSHIP('Calendar'[Date],tblWork_Orders[WO_ClosedDate]))

I then tried to seperate this in a table to break it up by date and got:

Is there something I am doing wrong with the relationships? Any help would be appreciated I have been working with PowerBI for about a week now and have a lot to learn. Thank you.

Here is my relationship view:

[amitchandak]

@Anonymous , I hope year date, and month are coming from the date table. They should be from date table

[Anonymous]

@amitchandak Yes, they are! If I would do it with the assign date or close date it will work but then I can no longer display them apporpriatly on the same table. I was hoping by using the date table I could display them on the same table

[amitchandak · Accepted solution]

@Anonymous ,

Looking at the visual as all dates are blank for Total_WO_Assigned . Means date table is not having all WO_AssignDate. Or WO_AssignDate is having timestamp.

To check change the format to date time and use a format with time. Time other than 00:00 or 12:00 AM might be there

Create a new  column without time and join that with date table

WO_AssignDate 1 = datevalue([WO_AssignDate])

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

[Anonymous]

Thank you I didn't realize that changing the way the date looked didn't remove the time stamp! I appreciate it.