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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
CraigC
Helper I
Helper I

Opened vs closed column chart with date filter

Hi All,

 

I am trying to create something like the below where i can compare how many incidents were opened per a day and how many incidents were closed per day. I have a column called Open Date and a Column Called Closed date. I do not have a date table.

Opened vs Close per day.JPG

Thank you

1 ACCEPTED SOLUTION

create a measure for closed incident using userelationship function like below and use this on visual

 

Closed Incidents = 
Calculate(Count(Table1[Incident]),
UseRelationship(Table1[CloseDate], Calendar[Date])
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

12 REPLIES 12
jthomson
Solution Sage
Solution Sage

I'd just create a date table and then relate your open date/closed date columns to it - as one of these relationships would be inactive you may need to stick a USERELATIONSHIP option in a measure to count whichever isn't going to be the primary relationship (assuming you just have the one data table)

Hi Jthomson, thanks for the idea. Any help in creating the date table would be greatly appreciated. Would i need to constantly refresh the date table to a new date range?

I mainly use custom tables as I want to include more than just a list of dates, but if you utilise CALENDARAUTO then it should be able to dynamically work out the earliest/latest dates based on your data set

I have checked around for the right way to create a date table but havent been able to find a definitive answer unfortunately.

hope this link will help to create date table



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Ok so i have a date table with both my opened incidents and closed incidents linked to the date column. The closed incident column being an inactive link with the opened incidents being the active link. If i create the graph now it will display the same info for the opened and closed incidents. How do i go about linking the closed incidents column now to show the right data?

Hi @CraigC,

 

Have you tried the solution provided by @parry2k above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

 

If you still have any question on this issue, feel free to post here. Smiley Happy

 

Regards

Worked perfectly thank you. I just added the current month filter to the date table and everything works.

create a measure for closed incident using userelationship function like below and use this on visual

 

Closed Incidents = 
Calculate(Count(Table1[Incident]),
UseRelationship(Table1[CloseDate], Calendar[Date])
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Been trying to use this formula but trying not to count rows for closed dates that are blank as the incidents are not closed yet.

This is exactly the problem i am stuck with, i tried to use your solution but it did not work in my power bi template. Can i please contact you about this problem?

Sorry for the late reply but worked perfectly thank you 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.