Bar chart on created vs resolved

[smerugu28]

Hi Team,

I have the below data and I want to show a single bar chart day-wise for created vs resolved incidents. Can someone guide me with it, please?

Number	State	Updated	Created	Change Request	Priority	Resolved
INC1378149	New	15-12-2023 12:37:50	15-12-2023 12:37:49		3 - Moderate
INC1377488	In Progress	15-12-2023 10:02:02	13-12-2023 09:39:19		3 - Moderate
INC1377752	On Hold	14-12-2023 07:28:04	14-12-2023 07:24:44	CHG1070960	3 - Moderate
INC1377328	On Hold	13-12-2023 10:21:09	12-12-2023 15:41:50	CHG1070927	3 - Moderate
INC1377806	Resolved	15-12-2023 10:02:04	14-12-2023 09:26:14		3 - Moderate	15-12-2023 10:02:04
INC1376563	Resolved	11-12-2023 06:35:07	08-12-2023 04:52:34		3 - Moderate	11-12-2023 06:35:07
INC1377694	Resolved	14-12-2023 06:44:53	13-12-2023 21:06:31		3 - Moderate	14-12-2023 06:44:53
INC1375552	Resolved	12-12-2023 15:50:09	04-12-2023 07:30:35	CHG1070543	3 - Moderate	12-12-2023 15:50:09
INC1376641	Resolved	12-12-2023 04:02:30	08-12-2023 10:52:27	CHG1070773	3 - Moderate	12-12-2023 04:02:30
INC1376112	Resolved	12-12-2023 15:24:26	06-12-2023 10:24:38		3 - Moderate	12-12-2023 15:24:26
INC1377228	Resolved	13-12-2023 09:13:54	12-12-2023 08:57:16		3 - Moderate	13-12-2023 09:13:54
INC1377318	Resolved	13-12-2023 07:55:44	12-12-2023 14:41:08		3 - Moderate	13-12-2023 07:55:44
INC1377314	Resolved	12-12-2023 16:34:55	12-12-2023 14:31:04		3 - Moderate	12-12-2023 15:47:30
INC1376321	Closed	14-12-2023 12:00:05	07-12-2023 08:51:13	CHG1070729	3 - Moderate	07-12-2023 11:22:42
INC1375845	Closed	14-12-2023 12:00:01	05-12-2023 09:48:52	CHG1070644	3 - Moderate	07-12-2023 11:23:55
INC1376609	Closed	15-12-2023 13:00:02	08-12-2023 09:05:39		3 - Moderate	08-12-2023 12:10:29
INC1375349	Closed	11-12-2023 15:00:03	01-12-2023 18:45:51		3 - Moderate	04-12-2023 14:18:26
INC1375894	Closed	12-12-2023 17:00:02	05-12-2023 13:10:21	CHG1070629	3 - Moderate	05-12-2023 16:59:22
INC1375598	Closed	12-12-2023 05:00:02	04-12-2023 09:44:04	CHG1070568	2 - High	05-12-2023 04:12:56
INC1376012	Closed	13-12-2023 08:00:02	06-12-2023 02:16:20		3 - Moderate	06-12-2023 07:11:03
INC1375673	Closed	14-12-2023 13:00:05	04-12-2023 15:16:52		3 - Moderate	07-12-2023 12:01:00
INC1375576	Closed	13-12-2023 13:00:04	04-12-2023 08:39:04		3 - Moderate	06-12-2023 12:42:00
INC1375559	Closed	13-12-2023 13:00:06	04-12-2023 07:40:58		3 - Moderate	06-12-2023 12:45:19
INC1375932	Closed	13-12-2023 08:00:03	05-12-2023 15:44:12		3 - Moderate	06-12-2023 07:27:52
INC1375899	Closed	12-12-2023 18:00:04	05-12-2023 13:46:25		3 - Moderate	05-12-2023 17:04:41
INC1376403	Closed	15-12-2023 15:00:01	07-12-2023 12:40:19		3 - Moderate	08-12-2023 14:05:51

[Ritaf1983]

Hi @smerugu28 
To get the desired result you must apply a few steps :
From PQ you need to get date from date time format: 

Then do the same with resolved date 

From the mode view 

Create a date table :

for more information about the date table please refer :

https://www.datacamp.com/tutorial/how-to-create-date-tables-in-power-bi-tutorial

As you have a date table create 2 relationships, active to created date, inactive to resolved date

Measures : 

Create 2 measures :
For created :

created QTY = DISTINCTCOUNT('Table'[Number])

For resolved :

resolved qty = CALCULATE([created QTY],all('Table'[Created date]),USERELATIONSHIP('calendar'[Date],'Table'[Resolved Date]))

More information about userelationship here :

https://www.youtube.com/watch?v=fOcgIuaJHEs&t=4s

Now you can plot the data on the graph

Pbix is attached, so you can follow my steps

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, remove the time stamp from Created and Resolved columns.  Create a Calendar Table.  Create an active relationship (Many to One and Single) from the Created column to the Date column and an inactive relationship (Many to One and Single) from the Resolved column to the Date column.  To your visual, drag Date from the Calendar Table.  Write these measures

C = counta(Data[Number])

R = calculate([c],userelationship(Data[Resolved],Calendar[date]))

Hoep this helps.

[smerugu28]

It went over my head as I am new to Power BI and it's my first report. Is there any video available for this or a tutorial to explain the steps you outlined above?  

[Ritaf1983]

Hi @smerugu28 
Did you see my response?
I gave a detailed solution step by step with screenshots, links to tutorials, and PBIX...

[Ashish_Mathur]

Hi,

Please read up about Power Query.  Watch some beginner videos on YouTube.  Also, read up on the CALCULATE function in the DAX language.  Start your learning journey today.