Forum Discussion
Show opened vs closed tasks per week
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!
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]))