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

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

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

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.

Highlighted
Super User IV
Super User IV

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors