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.
Hi, I am wondering if anyone here can help!
I am building a self service reporting tool for the IT Helpdesk. We have an incidents table that has Incident ID, Create Date, Solve Date, Parent Cateory columns.
I need to somehow work out how many incidents were open at the end of each month. A ticket is considered open if the [Solve Date] is null, OR the [Solve Date] > end of the month.
So for an example, if a ticket was created on April 2nd, 2017 and closed on April 20th, 2017 - this would not be included. If a ticket was created on April 2nd 2017, and closed on May 5th 2017, this would be included for April as it went over the end of the month mark.
If a ticket was opened April 2nd 2017, and closed 8th Jul 2017 it would be included for April, May and June.
I hope this makes sense? I have looked at similar examples but can't seem to find what I need on here!
Many thanks for any help
Solved! Go to Solution.
You could create a table below by using DAX to get the result you want.
Date = ADDCOLUMNS ( FILTER ( CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2018, 1, 31 ) ), DAY ( [Date] ) = 1 ), "YearMonth", FORMAT ( [Date], "YYYY-MMM" ), "OpenTicket", CALCULATE ( COUNT ( Incidents[ID] ) ) - CALCULATE ( COUNT ( Incidents[ID] ), FILTER ( Incidents, Incidents[SolveDate] < [Date] ) ) + CALCULATE ( COUNT ( Incidents[ID] ), FILTER ( Incidents, ISBLANK ( Incidents[SolveDate] ) ) ) )
Regards,
Charlie Liao
You could create a table below by using DAX to get the result you want.
Date = ADDCOLUMNS ( FILTER ( CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2018, 1, 31 ) ), DAY ( [Date] ) = 1 ), "YearMonth", FORMAT ( [Date], "YYYY-MMM" ), "OpenTicket", CALCULATE ( COUNT ( Incidents[ID] ) ) - CALCULATE ( COUNT ( Incidents[ID] ), FILTER ( Incidents, Incidents[SolveDate] < [Date] ) ) + CALCULATE ( COUNT ( Incidents[ID] ), FILTER ( Incidents, ISBLANK ( Incidents[SolveDate] ) ) ) )
Regards,
Charlie Liao
Hi,
I have the same requirement,but I am working on tabular model with date dimension and fact table that has Open and Close dates.The date table and fact table is joined with Open date(active),CloseDate(inactive).
Now,how can i acheive the same results without creating additional table.Can't we do this in a measure?
Hi, I am wondering if anyone here can help!
I am building a self service reporting tool for the IT Helpdesk. We have an incidents table that has Incident ID, Create Date, Solve Date, Parent Cateory columns.
I need to somehow work out how many incidents were open at the end of each month. A ticket is considered open if the [Solve Date] is null, OR the [Solve Date] > end of the month.
So for an example, if a ticket was created on April 2nd, 2017 and closed on April 20th, 2017 - this would not be included. If a ticket was created on April 2nd 2017, and closed on May 5th 2017, this would be included for April as it went over the end of the month mark.
If a ticket was opened April 2nd 2017, and closed 8th Jul 2017 it would be included for April, May and June.
I hope this makes sense? I have looked at similar examples but can't seem to find what I need on here!
Many thanks for any help
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |