unable to generate proper line chart

[prb]

Hi Colleagues,

could You please help me with the below issue I have faced?

I have a ticket database with opened and closure date (where applicable). I would like to use line chart to see how much tickets are opened and closed in a particular calendar week. For this I have converted the dates to necessary format (year/week), but when I put the "count of opened date" and "count of closed date" to the line chart, I was not sure what to use in the axis. if I put there either "opened date" or "closed date" to list there the year/week values in axis, the outcome was not proper. after that I have created a separate table only for year/week values using DISTINCT function from "opened date", and I would have used this column for axis. however, this is in different table and I cannot connect this column to both "opened date" and"closed date" in the other table since more relations are not supported. Do You know if there is an easyer method to solve this?

ticket	opened date (YYYY/WW)	closed date  (YYYY/WW)
a	2019/23	2019/23
b	2019/24	2019/26
c	2019/26	2019/27
d	2019/26	2019/27
e	2019/26	2019/27
f	2019/28

thanks in advance

[Anonymous]

Hi there - 

Could you please share a line chart (probably drawn on a piece of paper and taken a photo) of what you are expecting to see as the outcome? I am unable to visualize exactly what you are after, sorry about that.

Regards

[prb]

Hi

indeed, sorry I was not so explicit. I was looking for some similar chart. note: this is not in synch with the table above.

thanks in advance

[v-juanli-msft · Accepted solution]

Hi @prb 

In Edit queries, 

Close&&aplly, create a date table

date = ADDCOLUMNS( CALENDARAUTO(),"year",YEAR([Date]),"weeknum",WEEKNUM([Date]))

then create  ameausre

Measure = DISTINCTCOUNT(Sheet5[ticket])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.