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

[sandip]

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

https://drive.google.com/file/d/1cu-MhCMuPNJiW-p8Pn9a-AkWl-GBqYmL/view?usp=sharing

[Pragati11]

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

[sandip]

Hi @parry2k 

,

@amitchandak,

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:

https://drive.google.com/file/d/1-U1AKKoIgLdoakLmupmHVYEOA5el7nlN/view?usp=sharing

[sturlaws]

Hi @sandip,

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

Cheers,
Sturla

[amitchandak]

@sandip , refer this can help

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

Just do distinct count of months