cancel
Showing results for
Did you mean:
Helper III

## Hi, Can you helped me on to count only open ticket for each month?

Hi,

I have a below table :

 Ticket Date Created Date Closed Month it is open January February March April May June 1 1-Jan 31-Jan Jan, Jan 2 1-Jan 1-Feb Jan,Feb Jan Feb 3 15-Mar 1-Jun Mar,Apr,May,Jun Mar Apr May Jun 4 31-May 1-Jun May,Jun May Jun 5 8-Feb 1-Apr Feb,Mar,Apr Feb Mar Apr 6 1-May 15-May May May 7 31-Jan 1-Jun Jan,Feb,Mar,Apr,May,Jun Jan Feb Mar Apr May Jun 8 15-Jun 18-Jun Jun Jun 9 1-Mar 1-May Mar,Apr,May Mar Apr May 10 1-Jan 2-Jan Jan Jan

and I need to calculate the the count like below:

 Month Count of In Progress Jan 4 Feb 3 March 4 April 4 May 5 June 4

so For each ticket add one to the count open/month from the month the ticket was opened through (and including) the month it was closed.

here is my below data source

4 REPLIES 4
Super User III

Hi @sandip ,

See if the following thread helps:

https://community.powerbi.com/t5/Desktop/Count-tickets-opened-and-closed-in-the-same-month/m-p/97092...

Thanks,

Pragati

If this helps, Appreciate a KUDOS!

Proud to be a Super User!

Helper III

Hi @parry2k

,

I have used the below expression,

Carryover Tickets1 =
VAR __mindate = MIN ( 'DimDateDemo'[Date] )
RETURN
CALCULATE (
COUNTROWS( 'Demo Data' ),
'Demo Data'[Date Created] <= __mindate,
OR ( 'Demo Data'[Date Closed] >= __mindate, ISBLANK('Demo Data'[Date Closed]) )
)

and I have created a Date table like below:

DimDateDemo =
GENERATE (
CALENDAR ( FIRSTDATE('Demo Data'[Date Created]), LASTDATE('Demo Data'[Date Created])),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"Day", day,
"Month Number", MONTH ( [Date] ),
"Month Name", FORMAT ( [Date], "MMMM" ),
"Year", year,
"Month Year", FORMAT ( [Date], "MMMM-YYYY" ),
"Short Month", FORMAT ( [Date], "MM" ),
"Short Month Year", FORMAT ( [Date], "MMM-YY" ),
"Month Year Number", FORMAT ( [Date], "YYMM" )
)
)
and there is no relation between them, but I am getting below results:

Resident Rockstar

Hi @sandip,

have you solved this or are you still struggeling with it?

Cheers,
Sturla

Super User IV

@sandip , refer this can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

Just do distinct count of months

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!