Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
smerugu28
Regular Visitor

Bar chart on created vs resolved

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?

 

NumberStateUpdatedCreatedChange RequestPriorityResolved
INC1378149New15-12-2023 12:37:5015-12-2023 12:37:49 3 - Moderate 
INC1377488In Progress15-12-2023 10:02:0213-12-2023 09:39:19 3 - Moderate 
INC1377752On Hold14-12-2023 07:28:0414-12-2023 07:24:44CHG10709603 - Moderate 
INC1377328On Hold13-12-2023 10:21:0912-12-2023 15:41:50CHG10709273 - Moderate 
INC1377806Resolved15-12-2023 10:02:0414-12-2023 09:26:14 3 - Moderate15-12-2023 10:02:04
INC1376563Resolved11-12-2023 06:35:0708-12-2023 04:52:34 3 - Moderate11-12-2023 06:35:07
INC1377694Resolved14-12-2023 06:44:5313-12-2023 21:06:31 3 - Moderate14-12-2023 06:44:53
INC1375552Resolved12-12-2023 15:50:0904-12-2023 07:30:35CHG10705433 - Moderate12-12-2023 15:50:09
INC1376641Resolved12-12-2023 04:02:3008-12-2023 10:52:27CHG10707733 - Moderate12-12-2023 04:02:30
INC1376112Resolved12-12-2023 15:24:2606-12-2023 10:24:38 3 - Moderate12-12-2023 15:24:26
INC1377228Resolved13-12-2023 09:13:5412-12-2023 08:57:16 3 - Moderate13-12-2023 09:13:54
INC1377318Resolved13-12-2023 07:55:4412-12-2023 14:41:08 3 - Moderate13-12-2023 07:55:44
INC1377314Resolved12-12-2023 16:34:5512-12-2023 14:31:04 3 - Moderate12-12-2023 15:47:30
INC1376321Closed14-12-2023 12:00:0507-12-2023 08:51:13CHG10707293 - Moderate07-12-2023 11:22:42
INC1375845Closed14-12-2023 12:00:0105-12-2023 09:48:52CHG10706443 - Moderate07-12-2023 11:23:55
INC1376609Closed15-12-2023 13:00:0208-12-2023 09:05:39 3 - Moderate08-12-2023 12:10:29
INC1375349Closed11-12-2023 15:00:0301-12-2023 18:45:51 3 - Moderate04-12-2023 14:18:26
INC1375894Closed12-12-2023 17:00:0205-12-2023 13:10:21CHG10706293 - Moderate05-12-2023 16:59:22
INC1375598Closed12-12-2023 05:00:0204-12-2023 09:44:04CHG10705682 - High05-12-2023 04:12:56
INC1376012Closed13-12-2023 08:00:0206-12-2023 02:16:20 3 - Moderate06-12-2023 07:11:03
INC1375673Closed14-12-2023 13:00:0504-12-2023 15:16:52 3 - Moderate07-12-2023 12:01:00
INC1375576Closed13-12-2023 13:00:0404-12-2023 08:39:04 3 - Moderate06-12-2023 12:42:00
INC1375559Closed13-12-2023 13:00:0604-12-2023 07:40:58 3 - Moderate06-12-2023 12:45:19
INC1375932Closed13-12-2023 08:00:0305-12-2023 15:44:12 3 - Moderate06-12-2023 07:27:52
INC1375899Closed12-12-2023 18:00:0405-12-2023 13:46:25 3 - Moderate05-12-2023 17:04:41
INC1376403Closed15-12-2023 15:00:0107-12-2023 12:40:19 3 - Moderate08-12-2023 14:05:51
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ritaf1983
Super User
Super User

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

Ritaf1983_1-1702709542233.pngRitaf1983_2-1702709571195.png

Then do the same with resolved date 

From the mode view 

Create a date table :

Ritaf1983_3-1702709735488.png

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

Ritaf1983_4-1702709890277.png

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 :
Now you can plot the data on the graph
Ritaf1983_5-1702710123048.png

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?  

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.