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
fusiee_
Regular Visitor

Active Ticket Trend for last 12 months Rolling

Hey Everyone, 

I have a project where I got stuck quite a bit. I have project to make ticketing tool analysis. Where I have many tickets and I am trying to build a trend dashboard. I managed to do all the parts but somehow, I can't make the bar chart. I am trying to find an open tickets and show them as each month. 

 

For example, if I open a ticket on Dec 20 and let's say this ticket is closed in June 21, I need to see that ticket has been closed in June not in DEC 20 column. But also, let's say ticket is still open till now it should show that in the current month. which is November 21. It's kind of a Trend analysis of open items.  I have both opening date and close date of the tickets as well as their status. But don't know how to show this trends analysis in the bar chart. I tried to implement the calculation below. But it did not work as it supposed to. 

 

I have also SQL from the old system they used to generate this reports and bar chart.

 

AS [activeMonth],
convert (int,round((SELECT COUNT (Ticket Number) from #tmpAllOnlySubtype WERE
creation_date >= @istartMonth AND creation_date <= @iendmonth

 

(SELECT COUNT (Ticket Number) FROM #tmpAll WHERE
creation_date <= @Anonymous and                                                             @m 2 is the february
@Anonymous<=@iactMonth and
(closed_date='' or closed_date is null or closed_date> @Anonymous) 

 

 

so I am trying to implement this logic to show this Open Ticket Trend for each month. 

 

 

 

This is my Dax but not really working..

   
Active Ticket Records =

VAR currentDate = MAX('Calendar'[Date])

VAR openingdate = MIN('Reference'[OpeningDATE])

    VAR activetickets =

        CALCULATE(

            COUNTROWS('Reference'),

            All('Calendar'),

            Reference[OpeningDATE]<=currentDate,

            Reference[Closed Date]>currentDate

            || ISBLANK(Reference[Closed Date]),

            DATEDIFF(Reference[OpeningDATE],currentDate,MONTHS)<=12 

        )

VAR result =

    IF(

        MONTH(currentDate)<=MONTH(TODAY())

        && currentDate>=openingdate,

        activetickets

    )

    Return result


   

 

 

 

 

3 REPLIES 3
amitchandak
Super User
Super User

Thanks a lot, but, the open tickets should carry to next current month. and show there as its still open. I can't see that in your solution.

@fusiee_ , We do not have carry fwd, So what we do is every month we can build start and end

 

example

Employees = COUNT(Emp[Employee Id ])
Hire = CALCULATE([Employees], USERELATIONSHIP('Date'[Date], Emp[Start Date]))
Teminated = CALCULATE([Employees], USERELATIONSHIP('Date'[Date], Emp[End Date]), not (ISBLANK(Emp[End Date])))

 

// Max means this month end 
Cumm Hire = CALCULATE([Hire], FILTER(ALL('Date') , 'Date'[Date] <= max('Date'[Date])))
Cumm Termination = CALCULATE([Teminated], FILTER(ALL('Date'), 'Date'[Date] <= Max('Date'[Date])))
Current Employee = [Cumm Hire] -[Cumm Termination]  //This month end

 

//Min means this month start

 

Cumm Hire = CALCULATE([Hire], FILTER(ALL('Date') , 'Date'[Date] < Min('Date'[Date])))
Cumm Termination = CALCULATE([Teminated], FILTER(ALL('Date'), 'Date'[Date] < Min('Date'[Date])))
Current Employee = [Cumm Hire] -[Cumm Termination]  //This month start 

 

Hope this can help

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.