Forum Discussion

lampard1987's avatar
lampard1987
Frequent Visitor
9 years ago
Solved

How to show two different date sets in one visual

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,

 

Need Help
  • lampard1987's avatar
    lampard1987
    9 years ago

    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

4 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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