Line Chart by date

[Doliveira]

Hi everyone,

I'm trying to create a line chart with two lines, using the columns "Reported Date" and "Closed Date". Those columns have the date format "dd/mm/yyyy". The result of this chart should be the count of "Reported Date" and "Closed Date" for the last 15 days.

The problem is that when I put the Axys field as "Reported Date" (the only way I've found to put Date on X-Axys), and the Values field with "Count of Reported Date" and "Count of Closed Date", the line of "Closed Date" do not shows the correct value because the value it's not the same on "Reported Date". 

On the example below, the chart shows at day 03/08/2017 the line "Reported Date" with the count of 9, and the line "Reported Date" with the count of 3.

Example for the date "03/08/2017":

Sorting by Closed Date (11 rows):

Reported Date	Closed Date
20/07/2017 10:52	03/08/2017 17:58
28/06/2017 17:48	03/08/2017 16:49
27/06/2017 10:18	03/08/2017 16:44
03/08/2017 15:29	03/08/2017 16:32
03/08/2017 08:47	03/08/2017 12:57
03/08/2017 08:41	03/08/2017 12:55
04/07/2017 15:40	03/08/2017 11:28
25/07/2017 13:39	03/08/2017 11:27
26/05/2017 12:34	03/08/2017 11:26
31/01/2017 16:00	03/08/2017 11:08
02/08/2017 09:29	03/08/2017 09:04

Sorting by Reported Date (9 rows):

Reported Date	Closed Date
03/08/2017 19:11
03/08/2017 15:29	03/08/2017 16:32
03/08/2017 13:45
03/08/2017 13:23
03/08/2017 10:16
03/08/2017 10:07
03/08/2017 09:11
03/08/2017 08:47	03/08/2017 12:57
03/08/2017 08:41	03/08/2017 12:55

My english it's a little rusty, sorry for that.

Thanks

[vanessafvg · Accepted solution]

@Doliveira

what you need to is create a date table

then you need to create 2 relationships to your date table

one from reported date to date

another from closed date to date

you will see the second one becomes a --- line that will be the inactive relationship

then you need to place your date from you date table on the visual

your first measure will be 

reported count = calculate(countrows(tablename))

closed count = calculate(countrows(tablename), userelationship(table[closeddate], datetable[date])

then you can plot both

[Doliveira]

Just amazing! Thank you very much! Work as requested!