Plotting Open/Closed in same table/graph...

[jdogcisco]

Hello,

I am importing a single csv ("issues") of case/ticket date. Each row is a case and its details including, but not limited to...

Ticket #, Opened, Closed
1, 1/2/18, 1/5/18
2, 1/3/18,
3, 1/4/18, 1/5/18

So, every row has an "Opened" date, but not every row has a "Closed" date.

I've read some other posts around this area, but I'm just not getting the data to look right.

I created a separate "Calendar" table and created relationships to the "Date" field with the "Opened" and "Closed" fields in the "issues" table.

I created two measures...

CreatedCount = COUNTROWS(issues)

ClosedCount = CALCULATE(COUNTROWS(issues),USERELATIONSHIP('Calendar'[Date],issues[Closed]),NOT(ISBLANK(issues[Closed])))

When I add these measures to a table or chart, I only see a single value.  Not a count per date period...

At one point, I did get the "CreatedCount" to list the count per year, but then that stopped working and I dont' know why. I never got the "ClosedCount" to work.

Any help is appreciated.

[Anonymous]

You can try to use the following DAX to get the ClosedCount

ClosedCount = CALCULATE(COUNTROWS(Issues),FILTER(Issues,ISBLANK(Issues[Closed])<>FALSE()))

[jdogcisco]

Thanks for the reply.  That just give me a single number like the command I previously listed for "ClosedCount".  But, rather than the closed count, it gives me the open count (because it's counting the blanks).

The expected output is like below (taken from another post here):

But what I'm getting are just a single, total number (I used your suggestion for the "ClosedCount" column:

[Anonymous]

Do you want the following results shown in the table? Did you try the Matrix by expending the data?

 

[jdogcisco · Accepted solution]

Hi,

So, I appreciate the reply.  I'm not entirely sure why this worked, but to solve this I needed to change my "Created" column (the timestamp of when the case was opened) and "Closed" column from datetime to date. I'm not sure why this worked.  Maybe because the time never lined up with the time in the Calendar table and reducing the timestamp to just a date allowed it to find a match. Not sure.  Regardless, exhausting....haha