Get total open tickets counts for a month

[shailendra986]

Hi,

I am trying to get total open ticket count over the month period. I referred multiple articles, however the formula in not working and putting the same number across all months. I have Assigned Date and Resolution date Fields available for calculation. Resolution date will be empty if tocket is still open. I have only two tables Assignment and Date.. Could anyone please guid me how to achive this in PowerBi desktop.

[Anonymous · Accepted solution]

Hi @shailendra986

I am doing this in 4 step for easy understanding.

Create calculated column using the codes.

1.  Assignmonth = YEAR(Table1[Assigned Date])&MONTH(Table1[Assigned Date])


2.  Opentktcnt = CALCULATE(COUNT(Table1[Assignment ID]),FILTER(Table1,Table1[Assignmonth]= EARLIER(Table1[Assignmonth])))


3. Carrytkt = CALCULATE(COUNT(Table1[Assignment ID]),FILTER(Table1,ISBLANK(Table1[Resoultion Date])),FILTER(Table1,Table1[Assignmonth]<EARLIER(Table1[Assignmonth])))


4.OverallOpencnt = Table1[Opentktcnt]+Table1[Carrytkt]

The 4th step will give the desired result. When you bring OverallOpencnt to visual, Go to Visualization - > Values-> Right click on "OverallOpencnt" and select "Dont summarize".

Here is the final output ( Highlighted in yellow)

Thanks

Raj

[Anonymous]

Hi @shailendra986

Please share some sample data ( in copy & paste-able format) and expected output.

Thanks
Raj

[shailendra986]

Here is the example.

Assignment ID	Assigned Date	Resoultion Date
8621	3/5/2018	null
8842	3/20/2018	3/22/2018
9687	4/8/2018	null
6991	5/21/2018	null
6194	5/22/2018	5/25/2018
9874	6/8/2018	null

Expected Result in PowerBI report

Month Name	Total Ticket Count
Mar	2
Apr	2
May	4
June	4

[Anonymous]

How did you get 2 for March and 4 for May?

Thanks

Raj

[shailendra986]

For Mar there are two tickets which got opned. 

And for May also two tickets got opned, however May month is carrying over one open ticket from Mar and one from Apr, which sums up for total 4 tickets in total.

I need to count total numbers of ticket opened in that month and plus carrying over ticket which has opned in previous month and were still open for reporting month.

[Anonymous · Accepted solution]

Hi @shailendra986

I am doing this in 4 step for easy understanding.

Create calculated column using the codes.

1.  Assignmonth = YEAR(Table1[Assigned Date])&MONTH(Table1[Assigned Date])


2.  Opentktcnt = CALCULATE(COUNT(Table1[Assignment ID]),FILTER(Table1,Table1[Assignmonth]= EARLIER(Table1[Assignmonth])))


3. Carrytkt = CALCULATE(COUNT(Table1[Assignment ID]),FILTER(Table1,ISBLANK(Table1[Resoultion Date])),FILTER(Table1,Table1[Assignmonth]<EARLIER(Table1[Assignmonth])))


4.OverallOpencnt = Table1[Opentktcnt]+Table1[Carrytkt]

The 4th step will give the desired result. When you bring OverallOpencnt to visual, Go to Visualization - > Values-> Right click on "OverallOpencnt" and select "Dont summarize".

Here is the final output ( Highlighted in yellow)

Thanks

Raj

[Jennifer786]

Thanks Raj this is exactly what I was looking for, however when I try to plot this with a line graph it doesnt work. The OverallOpencnt is summarized on the Y axis. Is there any way to make this work with a graph?

[shailendra986]

Hi Raj,

Thanks for providing the formula.

Can we use this as Measure, because i need to place this in real reporting. In which summarizing can happen across resource, month, assignment type levels etc.

when i am plotting same in resource level number doesn't change. Please check below records generated by PowerBI.

Assignment Month	Total Open Assignment Count	Open Assignments	Carry Assignment	Assigned To
20186	141	120	21	E
20186	141	120	21	H
20186	141	120	21	I
20186	141	120	21	N
20186	141	120	21	P
20186	141	120	21	R
20186	141	120	21	Sh
20186	141	120	21	Su
20186	141	120	21	U
20186	141	120	21	V
20186	141	120	21	Vi

Thanks,

[Anonymous]

Hi @shailendra986

You can follow the solution i have provided above which will get you the expected output. You can play around with the solution to get more out of that like testing with measures etc.

Thanks

Raj

[shailendra986 · Accepted solution]

Thank you Raj for you help.