Forum Discussion
Submitted and completed in one matrix
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.
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.
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.Anonymouscould you please help further?
Up. Can anyone point me on what I'm missing? Combining two columns together results in Submitted priority.