cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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

Hi,

I have a below table :

Ticket Date CreatedDate ClosedMonth it is open      
    JanuaryFebruaryMarchAprilMayJune
11-Jan31-JanJan,Jan     
21-Jan1-FebJan,FebJanFeb    
315-Mar1-JunMar,Apr,May,Jun  MarAprMayJun
431-May1-JunMay,Jun    MayJun
58-Feb1-AprFeb,Mar,Apr FebMarApr  
61-May15-MayMay    May 
731-Jan1-JunJan,Feb,Mar,Apr,May,JunJanFebMarAprMayJun
815-Jun18-JunJun     Jun
91-Mar1-MayMar,Apr,May  MarAprMay 
101-Jan2-JanJanJan     

 

and I need to calculate the the count like below:

MonthCount of In Progress
  
Jan4
Feb3
March4
April4
May5
June4

 

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

4 REPLIES 4
Super User III
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!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Helper III
Helper III

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:

 

Resident Rockstar
Resident Rockstar

Hi @sandip,

 

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

 

Cheers,
Sturla

Super User IV
Super User IV

@sandip , refer this can help

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

 

Just do distinct count of months



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors