Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good morning,
I am fairly new to DAX so I am trying my best not to get confused with all of this 🙂
I am trying to create a Support Desk report which allow the team to see something like this:
I managed to create the following matrix using a few fields in the incidents table "Created on, Breached F.R. On, Breach R. On,":
So far, no problems and data looks correct however, I want to add two more columns to the matrix above which are:
I used the following measure to calculate Active tickets at the end of each calendar month:
Active_tickets = ADDCOLUMNS ( FILTER ( CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ), DAY ( [Date] ) = 1 ), "YearMonth", FORMAT ( [Date], "YYYY-MMMM" ), "OpenTicket", CALCULATE ( COUNT ( incidents[incidentid] )) - CALCULATE ( COUNT ( incidents[incidentid] ), FILTER ( Incidents, incidents[tiss_resolveddate] < [Date] ) ) + CALCULATE ( COUNT ( incidents[incidentid] ), FILTER ( Incidents, ISBLANK( incidents[tiss_resolveddate] )) ) )
But I noticed that we have a major issue with our data where most of the historical records in our CRM system has no tiss_resolveddate value populated and the result was:
Thus, I wonder if anyone can help me apply my following Ideas to overcome this issue or present any other idea that I can work with:
I appreciate that my measure above might be totally wrong and irrelevant to the idea of calculating both Brought Forward tickets and Carried Forward tickets values.
Thanks
Hicham
Hi @Anonymous
Do you have a simple worksheet or preferable dummy pbix?
Hi,
Thanks for your feedback on this and I apologise for the late reply.
You will find a pbix file at this link:
I am trying to calculate the quantity of active tickets at the end of each month 30th or 31th depending on the calendar month number.
In an ideal situation a ticket is considered de-activated/closed when a resolution date is populated into the resolution field however, where I work we have a historical data situation where a ticket is never being populated with a resolution date value upon closure.
I tried creating a few dax measures and the best one I managed to create "included in the file" lead me to project the right quantity of all active tickets but only for the current month "Sep19" and not the historical one
beside my measure above is showing all active tickets on the first day of a month and not the end of the month which is not correct as we might raise new tickets on the very first day of any month I need the dax to populate all active tickets at the end each month:
Active_tickets_modifiedon1 = ADDCOLUMNS ( FILTER ( CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ), DAY ( [Date] ) = 1 ), "YearMonth", FORMAT ( [Date], "YYYY-MMMM" ), "OpenTicket", CALCULATE ( COUNT ( incidents[incidentid] ), FILTER ( Incidents, incidents[modified on] < [date] ), FILTER(incidents, incidents[status] = "Active") ) )
Any help would be much appreciated 🙂
Kind regards
Hicham
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |