cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Line graph of count of start and end date as different months

I have a Created and Resolved date of tickets, and would like to have one line graph that shows how many tickets were created and resolved each month. Some of the tickets have created months, but have not been completed, so they do not have a resolved date. Right now I can only get a count of created in that month (blue line) and the count of tickets that were created that month, that are now closed (Black line). They way it has it now it does not matter what month they were closed, as it will just be marked as resolved the in the month it was opened

 

I would like to have it the total count of Resolved in that particular month. As in there may be tickets that were created in July, but were Resolved in August, so I would like to have the resolved count show up in August, not July. This would mean that there are some months more tickets are resolved than created. I imagine it looking liket red and green graph attached. 2018-02-14_14-42-55.jpgfind-vs-resolved.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User VII
Super User VII

Re: Line graph of count of start and end date as different months

hope this is what you are looking for 🙂

 

https://drive.google.com/file/d/1ew9IMgvKMn0lDfZ70Ww64cba_egE4YxF/view?usp=sharing






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

5 REPLIES 5
Highlighted
Super User VII
Super User VII

Re: Line graph of count of start and end date as different months

Here is the high level how it will work:

 

- You need to have calendar table in your model if it is not there

- set relationship between your create date and resolve date with calendar table on date, you will see one relation is inactive.

- add two measures in your model, one for created tickets and one for resolved tickets, assuming resolved ticket has inactive relationship with calendar table, in this measure calculation use "USERELATIONSHIP" function

- drop line chart with date from calendar table on x axis and use two measure as values.

 

 

 

 






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.





Highlighted
Frequent Visitor

Re: Line graph of count of start and end date as different months

I am not super familiar with measures, so how would I go about making the measures for each one?

Highlighted
Super User VII
Super User VII

Re: Line graph of count of start and end date as different months

Can you share sample data using google drive in excel sheet and i will put together something for you.






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.





Highlighted
Frequent Visitor

Re: Line graph of count of start and end date as different months

https://drive.google.com/open?id=18pHgv-5Fo1Ajsc_BWLD7OUxMH4pEvBTd

 

That is the data I have. The created months and the resolved months. I have a Calander table created. I have created months and resolved months in the same "Combined" query.

Super User VII
Super User VII

Re: Line graph of count of start and end date as different months

hope this is what you are looking for 🙂

 

https://drive.google.com/file/d/1ew9IMgvKMn0lDfZ70Ww64cba_egE4YxF/view?usp=sharing






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

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors