Forum Discussion
Submitted and completed in one matrix
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
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.