Forum Discussion
Different date value counts
Hello!
I'm trying to make a report with multiple values based on a creation date and a resolution date on a ticketing system.
For example:
| Issue | Created | Resolved | Client |
| 123 | 21/07/2016 | 21/07/2016 | X |
| 234 | 23/07/2016 | 23/07/2016 | P |
| 345 | 23/07/2016 | 24/07/2016 | P |
| 456 | 24/07/2016 | 24/07/2016 | T |
I'm trying to build a report wich looks into the number of issues created and resolved on day X, but can't seem to do it without a new table to count all issues with the same date.
I would like to achieve, with only one table, the number of issues created on day X, resolved on day X and the ones that didn't get resolved on that day counting as backlog.
I can build the basic report Created vs Resolved on the same day, but can't seem to find any way to do a "backlog through time" with only one table.
In Excel the solution I found for this was through counting created, minus resolved (on the same day) plus the non resolved cumulated before.
Giving me something like this:
Any suggestion for this without using a different table?
Only one table would be the best because I could filter out different clients on the report directly.
Thanks!
João
Hi jpmeijinhos,
I could think out the following way to calculate the wanted value in only one table, which would need some transformation of the table you shared, please follow the steps below and see if it could help here:
1. Open Power BI desktop, under the home tab, click Edit Queries to open Query Editor, press Ctrl and select Created and Resolved columns, under Transform tab, click Unpivot column, then under home tab of Query Editor, click Close and Apply.
2. Rename the Attribute and the value column to Status and Date, verify the Date column type is Date/time;
3. Create the measure below:
IssueCreated := countx( filter('Table', 'Table'[Attribute]="Created"), 'Table'[Issue])IssueResolved = countx( filter('Table', 'Table'[Attribute]="Resolved"), 'Table'[Issue])backlog = CALCULATE ( [IssueCreated]-[IssueResolved], FILTER ( ALL ( 'Table'[Value]), 'Table'[Value]<= MAX ( 'Table'[Value]) ) )Please see the screenshots:
If you need any further help regarding this method, please post back.
Regards
5 Replies
Hi jpmeijinhos,
I could think out the following way to calculate the wanted value in only one table, which would need some transformation of the table you shared, please follow the steps below and see if it could help here:
1. Open Power BI desktop, under the home tab, click Edit Queries to open Query Editor, press Ctrl and select Created and Resolved columns, under Transform tab, click Unpivot column, then under home tab of Query Editor, click Close and Apply.
2. Rename the Attribute and the value column to Status and Date, verify the Date column type is Date/time;
3. Create the measure below:
IssueCreated := countx( filter('Table', 'Table'[Attribute]="Created"), 'Table'[Issue])IssueResolved = countx( filter('Table', 'Table'[Attribute]="Resolved"), 'Table'[Issue])backlog = CALCULATE ( [IssueCreated]-[IssueResolved], FILTER ( ALL ( 'Table'[Value]), 'Table'[Value]<= MAX ( 'Table'[Value]) ) )Please see the screenshots:
If you need any further help regarding this method, please post back.
Regards
Hello!
Thanks a lot, created and resolved are working very well.
The problem still remains with the "backlog".
The backlog is currently showing "per day" and it would need to be the total SUM of unresolved issues on a specific day plus the remainder of the day before.
Like so:
Date Created Resolved Backlog 26/06/2016 3 2 1 27/06/2016 5 3 3 28/06/2016 4 5 2 29/06/2016 6 2 6 Hi jpmeijinhos,
The measure of the backlog I have shared should work in this way.
I just created some extra testing data:
Could you please share the issue you encountered when using this measure, or the wrong results?
Regards
