Show Created, Resolved and Backlog defects in single visualization across a Time axis of say Months

[Anonymous]

Hi All,

I have a time axis of Months and my requirement is to :-

1. Show Created cases in a particular month.

2. Show Resolved cases in that particular month

3. Show the backlog from that particular month

I am using this measure :

Resolved = CALCULATE( COUNTROWS(FILTER(Tickets, Tickets[resolution] <> BLANK())) , USERELATIONSHIP( Tickets[resolutiondate], 'Date'[Date]))

There was already a relationship between   Tickets[CreatedDate] and   Date[Date] which is the active relationship.

I have now created another secondary relationship  between   Tickets[resolutiondate] and    Date[Date].

But now in my visualization the data of resolved tickets comes up without any Month related to it and comes as a single count till date (see below – the first bar).

The created tickets data comes up properly as that is already used in active relationship.

Please suggest where the issue could be.

-Mani A

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Everything looks great. The possible cause could be the format of the Tickets[resolutiondate]. Does it have a time part while the 'Date'[Date] doesn't? For example, Tickets[resolutiondate] is 2017/01/01 13:30:48 while the 'Date'[Date] is 2017/01/01. Please check it out.

Best Regards!

Dale

[Anonymous]

@v-jiascu-msft,  you are awesome, thanks a lot.

I am new to Power Bi, did that change to Date only for CreatedDate but not for resolution date as I created the measure later.

Silly, but I guess that is how I will learn....

[v-jiascu-msft]

Hi @Anonymous,

Forget to say you can add a new Calculated Column like this: 

=Tickets[resolutiondate].[Date]

Then you can keep the time part for other purpose.

Best Regards!

Dale

[Anonymous]

@v-jiascu-msft thansk for that info, I just duplicated the column and changed the data type to date and it has worked.