Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sandip
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
Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

sandip
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:

 

Hi @sandip,

 

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

 

Cheers,
Sturla

amitchandak
Super User
Super User

@sandip , refer this can help

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

 

Just do distinct count of months

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.