Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Submitted and completed in one matrix

Hi,   I have a table of tickets with submited, completed, closed, etc. dates and other various info. I do understand how to separate them in two different visuals (like below):   So this wi...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous,

Could you please share sample data of your original table and  post expected result based on sample data here? As your post, you would need to create a calendar table, you can take a look at the following similar threads about calculating opened and completed tickets.

https://community.powerbi.com/t5/Desktop/Received-vs-Closed-against-Year-Month/td-p/123515
https://community.powerbi.com/t5/Desktop/calculate-open-tickets-by-date/m-p/207644#M91517

Regards,
Lydia

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hey,

 

thanks for reply.

 

 

.Done is for the reason that some Completed tickets were not actually in that state, but in similars that are, let say, similar. Thus, I made this .Done to count Cancelled and Closed too as this:

.Done = IF('Model'[Completed] <> BLANK(), 'Model'[Completed], IF('Model'[Closed] <> BLANK(), 'Model'[Closed], 'Model'[Cancelled]))

Guided by this https://community.powerbi.com/t5/Desktop/Received-vs-Closed-against-Year-Month/td-p/123515 mine "Closed" will be .Done.

 

TicketSubmittedClosedCompleted CancelledStatus.Done
117-08-1817-08-18  Closed17-08-18
220-08-1822-08-1822-08-18 Closed22-08-18
320-08-1805-09-1820-08-18 Closed20-08-18
420-08-1805-09-1820-08-18 Closed20-08-18
521-08-18 26-11-1826-11-18Cancelled26-11-18
621-08-1830-09-1814-09-18 Closed14-09-18
721-09-1807-09-1822-08-18 Closed22-08-18
821-09-1811-11-1826-10-18 Closed26-10-18
929-09-18   Open 
1029-09-18   Open 
1130-09-18   Open 
1230-09-18   Open 

 

I have created master calendar

 

Expected result:

MonthSubmitted Count Completed Count
August6 5
September6 1
October0 1
November0 1

 

Thank you in advance.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    guided by this https://community.powerbi.com/t5/Desktop/Received-vs-Closed-against-Year-Month/td-p/123515 link I've tried to make similar matrix.

    This is what I got: (I have different titles for table, don't bare that in mind)

    Axis = .Done (from formula below)

    Value = Ticket
    Also a filter for tickets with exact statuses (which is probably not a point).

     

    But matrix shows bit different values:

    These .Closed and .Received are new formulas based on example in the same link you posted (https://community.powerbi.com/t5/Desktop/Received-vs-Closed-against-Year-Month/td-p/123515)

    .Closed = CALCULATE(COUNTA('Model'[Completed]), USERELATIONSHIP('Model'[Completed], MasterCalendar[EveryDate]))

    &

    .Received = COUNTA('Model'[Submitted])   ---- this one works as expected and shows same numbers

     

    For .Closed I've also tried COUNTA('Model'[.Done]), but result is the same as with Completed. Different from first visual.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Long story short, Matrix shows completed by the rule of submitted.

      E.g., completed in August will be those, that were submitted in August. So these 2 columns are still related, though I added calculations as per article you provided

      .Closed = CALCULATE(COUNTA('Model'[Completed]), USERELATIONSHIP('Model'[Completed], MasterCalendar[EveryDate]))

      &

      .Received = COUNTA('Model'[Submitted])   ---- this one works as expected and shows same numbers


      Please assist.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymouscould you please help further?