Forum Discussion

GunnerJ's avatar
GunnerJ
Icon for Post Patron rankPost Patron
7 years ago

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 pro...
Need Help
GunnerJ's avatar
GunnerJ
Icon for Post Patron rankPost Patron
7 years ago

*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