Different date value counts

[jpmeijinhos]

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

[v-micsh-msft · Accepted solution]

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

[jpmeijinhos]

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

[v-micsh-msft]

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

[jpmeijinhos]

Thanks again for replying.

I've checked and your measure works for me when I see the data in a matrix:

(Date = Value, language is portuguese, sorry)

The problem happens when I apply a columns chart over it:

It doesn't reflect the matrix numbers, it counts the days seperatly.

I can't seem to find a way for the data to be "cumulative" in the backlog.

Thanks for the help!

[jpmeijinhos]

Replying again after doing another check!

I had the days separated in the shared axis of the chart...

It works like a charm now!

Thanks a lot for your help! You were great!