Submitted and completed in one matrix

[Anonymous]

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 will show submitted tickets by month (on the left) and completed tickets by month (on the right). Submitted has SubmitDate axis; completed has LastCompleteDate axis. So, different timestamps. This is made to show completed per month no matter when they were submitted.

What I do want to have is a matrix (well, basically something, but one visual) where will be tickets Submitted VS. Completed and ratio, split by month.

(just example, this one counts open vs completed)

What I am struggling is:

I took June submitted. It will show 72 Completed (with 54 highlighted in June), AND matrix will show 81 submitted, 78 completed, 3 open. From 78 Completed there are 54 that are completed in June and 25 completed in other months starting July (because of those submitted, which were closed after) + 3 still open.

From one point this is correct in flames of visuals, but what I want to achieve is: (example)

Submitted in *Month* = 50 (submitted after June 1st and before June 30th including)

Completed in *Month* = 60 (submitted anytime, but completed after June 1st and before June 30th including)

in one matrix. With ratio 🙂

I assume that there is something to deal with dates. Like I said, for these two visuals I do have 2 different date tables (submitted and lastcompleted). Perhaps I need a calendar, which is somehow linked to both date tables or something.

Hopefully someone will understand, as i spent so much time figuring out why stats are so awfully bad and got myself confused already. 🙂
Thanks in advance.

[v-yuezhe-msft]

@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]

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.

Ticket	Submitted	Closed	Completed	Cancelled	Status	.Done
1	17-08-18	17-08-18			Closed	17-08-18
2	20-08-18	22-08-18	22-08-18		Closed	22-08-18
3	20-08-18	05-09-18	20-08-18		Closed	20-08-18
4	20-08-18	05-09-18	20-08-18		Closed	20-08-18
5	21-08-18		26-11-18	26-11-18	Cancelled	26-11-18
6	21-08-18	30-09-18	14-09-18		Closed	14-09-18
7	21-09-18	07-09-18	22-08-18		Closed	22-08-18
8	21-09-18	11-11-18	26-10-18		Closed	26-10-18

9	29-09-18				Open
10	29-09-18				Open
11	30-09-18				Open
12	30-09-18				Open

I have created master calendar

Expected result:

Month	Submitted Count	Completed Count
August	6	5
September	6	1
October	0	1
November	0	1

Thank you in advance.

[Anonymous]

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]

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]

@v-yuezhe-msftcould you please help further?

[Anonymous]

Up. Can anyone point me on what I'm missing? Combining two columns together results in Submitted priority.