Showing Incidents Created and Resolved in the same graph or line graph

[bimystic]

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.

[Icey]

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.

[TomMartens · Accepted 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

[Ashutosh_domle]

I tried this method but Stilll I am not able to bring date wise distribution. Could you please help me?