Forum Discussion
How to show 2 values from the same record on the same graph
I need to show a month by month view of service orders opened and service orders closed. Each record will have an open date and most will have a closed date. (except those that are still open) My problem is that I need a record to be counted twice in the sense that I need a tally for the open date on the date it happened and a tally for the close date on the date it also happened. The end result will be a graph that show how many service orders were opened and closed within a month. Any help would be appreciated. I feel like this may be more simple than it looks but I'm stuck for now. The only way I've been able to somewhat create this content is by making two seperate graphs with each having it's own X-axis consisting of Open and Close Dates.
2 Replies
GunnerJ what you need to do i think, uis to create 2 relationships from your table to the date table, one will be an active relationship and the other inactive, so one to the open date and one to the closed date.
read more about it here..
if you paste some of your date using text (not an image) , i can put it together for you.
*If you think exchanging some sample files might be easier please message me to exchange contact info.*
Will a date table made up of dates from one of the columns in question work? Also I wonder if doing a calculate function might not solve the problem? Count how many service orders have an Open date in a given month to see how many Service orders were opened and the same for closed dates.
*Current code for calculating service orders opened in a given month. It doesn't however give me the right result.
Opened Services Orders by Month = CALCULATE( COUNT(Workflow[BI_SO_NBR]),DATESBETWEEN(Workflow[BI_OPEN_DT],STARTOFMONTH(Workflow[BI_OPEN_DT]), ENDOFMONTH(Workflow[BI_OPEN_DT])))*My date table code
Dates = CALENDAR(DATE( Year( Min( 'Service Orders'[BI_OPEN_DT])), 1 ,1),DATE( YEAR( Max('Service Orders'[BI_OPEN_DT])), 12, 31))*Here's some sample data as well. Feel free to just add a random Service order number to each row.BI_CLOSE_DT BI_OPEN_DT
02-AUG-2018 83:00:62 11-JUL-2018 25:93:83
02-AUG-2018 83:00:62 11-JUL-2018 25:93:83
02-AUG-2018 83:00:62 11-JUL-2018 25:00:62
02-AUG-2018 91:16:25 11-JUL-2018 25:16:25
02-AUG-2018 83:00:62 11-JUL-2018 25:00:62
02-AUG-2018 83:00:62 11-JUL-2018 00:93:83
02-AUG-2018 83:00:62 11-JUL-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:93:83
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
30-JUN-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:93:83
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:93:83
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
10-JUL-2018 83:00:62 29-JUN-2018 25:00:62
