Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the below data for incidents
SN | Incident Number | Desc | State | Created_Date | Resolved_Date |
1 | INC1 | Unable to login | Resolved | 10-May-20 | 11-May-20 |
2 | INC2 | Error on VM | New | 15-May-20 | |
3 | INC3 | Account locked | Resolved | 16-May-20 | 18-May-20 |
4 | INC4 | Unable to login | Resolved | 17-May-20 | 2-Jun-20 |
5 | INC5 | Account locked | In Progress | 28-May-20 | |
6 | INC6 | Error on VM | Resolved | 1-Jun-20 | 1-Jun-20 |
7 | INC7 | Unable to login | In Progress | 2-Jun-20 | |
8 | INC8 | Account locked | On Hold | 2-Jun-20 | |
9 | INC9 | Error on VM | On Hold | 5-Jun-20 | |
10 | INC10 | Account locked | Resolved | 6-Jun-20 | 8-Jun-20 |
I need to create a report where I can show the number of incidents created and resolved in the same graph.
When I am using the Created_Date in Y axis and Count(Incident Number ) and Count(Resolved_Date) in values the value are not correct. The Resolved shows values in month even when there is no Resolved. The reason is that it picks Created data i.e. it counts incidents that were created in Jan and resolved any month later.
How can I show incidents resolved and created on month basis.
Solved! Go to Solution.
Hey @bimystic ,
create a dedicated calendar table, create two relationships from the date column of the calendar table (on the one side) and the Created_Date and the Resolved_Date column.
You have to be aware that only one relationship can be marked as active.
Use the date column from the calendar table on the axis of your visual.
Create at least one measure like so:
CALCULATE(
COUNT('tablename'[Incident Number])
, USERELATIONSHIP('CalendarTable'[DateColumn] , 'tablename'[inactive date column]
)
Hopefully, this is what you are looking for.
Regards,
Tom
Hi @bimystic ,
Just as @TomMartens said, you need to create a Date dimension table. You can try this:
Dates =
CALENDAR (
MIN ( MIN ( 'Table'[Created_Date] ), MIN ( 'Table'[Resolved_Date] ) ),
MAX ( MAX ( 'Table'[Created_Date] ), MAX ( 'Table'[Resolved_Date] ) )
)
Then, create relationships.
Then, create measures.
Created count = COUNT ( 'Table'[Incident Number ] )
Resolved count =
CALCULATE (
COUNT ( 'Table'[Incident Number ] ),
USERELATIONSHIP ( 'Dates'[Date], 'Table'[Resolved_Date] ),
'Table'[Resolved_Date] <> BLANK ()
)
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @bimystic ,
create a dedicated calendar table, create two relationships from the date column of the calendar table (on the one side) and the Created_Date and the Resolved_Date column.
You have to be aware that only one relationship can be marked as active.
Use the date column from the calendar table on the axis of your visual.
Create at least one measure like so:
CALCULATE(
COUNT('tablename'[Incident Number])
, USERELATIONSHIP('CalendarTable'[DateColumn] , 'tablename'[inactive date column]
)
Hopefully, this is what you are looking for.
Regards,
Tom
I tried this method but Stilll I am not able to bring date wise distribution. Could you please help me?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |