Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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..
@fusiee_ , refer if my HR blog on same topic can help
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |