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
vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
7 years ago

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.. 

 

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

 

if you paste some of your date using text (not an image) , i can put it together for you.

  • 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