How to show two different date sets in one visual

[lampard1987]

Hello,

I have an issue that I have run into and need some help please. Basically I am putting together a help desk report and want to use a line chart visual to show monthly how many tickets were created and how many were closed.

In our raw data we have one table that has a created date column and a date closed column.  I use a data splicer that is working off the date created months to show data month by month.

The issue is that becauese the date closed column is working off a diferent timeline to the date created column my numbers are not adding up. For instance if I used my slicer to show the logged year of 2017 and the logged month as January I would see that 216 tickets were created and it shows that 212 were closed. However if I now change the slicers filed to show the closed year of 2017 and the closed month to January it would show me that actually there were 198 tickets closed in this range.

Slicer set with created year and monthSlicer set as closed year and month

How do it get the visual to show that the number created was 216 and the actual number closed was 198 in the visual using only one slicer?

Thanks,

[v-caliao-msft]

@lampard1987,

You could create a date table, and then use the year and month in your slicer. The steps below is for you reference.

1. Create a new table
   Date = CALENDAR(DATE(2016,1,1),DATE(2017,13,31))
2. Create a new column
   YearMonth = FORMAT('Date'[Date],"YYYYMM")
3. Create two measures.
   MaxDate = MAX('Date'[Date])
   MinDate = Min('Date'[Date])
4. Create a measure in your original table
   Measure = IF((MAX(Ticket[CloseDate])>='Date'[MinDate]&&MAX(Ticket[CloseDate])<='Date'[MaxDate])||(MAX(Ticket[CloseDate])>='Date'[MinDate]&&MAX(Ticket[CloseDate])<='Date'[MaxDate]),1,0)
5. Use this measure in your visual filter.
   

Regards,

Charlie Liao

[lampard1987]

Thank you Charlie. I will give this a try

[v-caliao-msft]

@lampard1987,

Have you tried my solution?

Regards,

Charlie Liao

[lampard1987 · Accepted solution]

Hello Charlie,

Apologies for my delayed response, I have been out of the country.

I could not get your suggestion to work the way I required it to but in the end I created a duplicate table and also created a 'Months' table and 'Years' table. Then I created a relationship from the Logged Month field and the Closed Date field to the Month table and the same for the Year table. Please see image attached.

This seems to have worked as required.

 

Thanks,

James