Forum Discussion
Show opened vs closed tasks per week
Hi all,
I'm working on some ticket reporting and want to plot a graph that shows tickets that have been opened or closed per week.
My raw data is this:
| Ticket ID | Opened week | Closed week |
| 123001 | 2020-CW1 | 2020-CW3 |
| 123002 | 2020-CW2 | 2020-CW2 |
| 123003 | 2020-CW2 | 2020-CW3 |
I would like my bar chart to have the weeks on the X axis, and per week have two bars showing the count of opened and closed tickets.
I can do that in two separate charts, but how can I combine the two?
Thanks in advance!
Hi Anonymous ，
There're 2 ways:
1.Go to "edit queries">select"open week" and "close week">"unpivot columns":
Back to the report view, and put the columns in the clustered bar chart visual:
2.Another way is to create a dimweek table using a dax expression as below:
Dim week = DISTINCT(UNION(VALUES('Table'[Opened week]),VALUES('Table'[Closed week])))Then create 2 measures：
Opened tickets = CALCULATE(COUNTA('Table'[Ticket ID]),USERELATIONSHIP('Dim week'[week],'Table'[Opened week]))Closed tickets = CALCULATE(COUNTA('Table'[Ticket ID]),USERELATIONSHIP('Dim week'[week],'Table'[Closed week]))Finally,put the dimweek and measures in the clustered bar chart visual:
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
3 Replies
Hi Anonymous
This can get a little bit tricky but I have come across this same issue before.
One thing I would suggest is before moving forward, get the actual week start date.
I am assuming Opened week and closed week is based off of a date field?
If so, in the PowerQuery, extract the Start of Week in the Transform tab.
From there, you will need to create a Calendar table to connect the dates.
https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/
Then by creating measures that sum the ticket counts, you can use the USERELATIONSHIP DAX function to seperate these values accordingly.
Tickets_Closed = CALCULATE(COUNT([column][Date Closed]),USERELATIONSHIP('Calendar'[Date],[column][Date Closed]))
Tickets_Entered = COUNT([column][Date Entered])Then make the connections in the data model:Finally, you should expect something that looks similar to this:Hey Anonymous
bfernandez Is awesome and very thurough. If you want to simplify it a little you can create a week table with you CW1, CW2 and then use that as your "date" table and create a helper column for both close and open (=Right(opened/Closed week, 3)). You could even create the week table by referencing the original query and then deleting the other columns. All this does different from bfernandez post is it allows you to show CW1 etc in the Axis instead of a date and it saves your from having to create a relational column in the original data with the week start date.
Then you can create a clustered column chart and drag your week table column to the Axis field and your opened week and closed week as your values.
bfernandez solution is the better route to go, but this is kind of cheating and may be more in line with what you want the end result to look like.
I recommend bfernandez as a solution (and for kudos), but you are welcome to kudo here and accept it as a second solution lol. Let us know if this helps.
Hi Anonymous ，
There're 2 ways:
1.Go to "edit queries">select"open week" and "close week">"unpivot columns":
Back to the report view, and put the columns in the clustered bar chart visual:
2.Another way is to create a dimweek table using a dax expression as below:
Dim week = DISTINCT(UNION(VALUES('Table'[Opened week]),VALUES('Table'[Closed week])))Then create 2 measures：
Opened tickets = CALCULATE(COUNTA('Table'[Ticket ID]),USERELATIONSHIP('Dim week'[week],'Table'[Opened week]))Closed tickets = CALCULATE(COUNTA('Table'[Ticket ID]),USERELATIONSHIP('Dim week'[week],'Table'[Closed week]))Finally,put the dimweek and measures in the clustered bar chart visual:
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!