How to Use a Calendar Date Range to Filter Different Dates in a Table

[mhambalek]

I have one table of support tickets that show ticket details like Date Created, Date Closed, etc.  I'd like to have a slicer/filter that I can set to a date or date range that would show all tickets created in that range and closed in that range.  Can I have an calendar date filter that is independent of those two date fields?  I'd like to have one or two visuals based on that filter - # Opened and # Closed using a line chart.

I was using Date Created as a slicer to test this.  That worked when I wanted to see tickets created in that range, but then it would only give me tickets closed that were created in that range.  That's not what I want.  

How can I accomplish this?

[MFelix]

Hi @mhambalek,

You have to create a date tsble then make a relation betweencalendar and open and close date the important part is to make the relationship inactive and use USERELATION formula to make your calculations.

Then add the calendar dste in your slicer and you can filter on both measures
..

Regards,
MFelix

[mhambalek]

Hi @MFelix,

Thanks for the quick response.  I'm new to PBI, so these may sound like silly questions:

- What are the columns and records that should be in the date table?

- The fields in the support ticket table are actually date/time stamps. Should I contvert that to just a date?

- Does the USERELATIONSHIP formula go into new fields in the support ticket table? One for Create Date and One for Closed Date?

Thank you very much.

[mhambalek]

@MFelix,

I figured out how to create the calendar table.  I'm fairly certain I need to convert all the date fields to mm/dd/yyyy so I did that in all tables.  So now I just need to understand the USERELATIONSHIP formula.

[MFelix]

Hi @mhambalek,

You need to do something like this

Count values = CALCULATE( COUNT (Table[column]), USERRELATIONSHIP (Table[Date]), Calendar (Date))

Regards,
MFelix

[mhambalek]

What tables does that go in?  The support ticket table or calendar table?

[MFelix]

Support ticket table.

You then need to do a measure likes this for each.of.your date columns in the support tickect and make your visuals with the following data:

Date - calendar table
Measured with userrelationship

Regards,
Mfelix

[mhambalek]

Here's the formula for the measure field I created:

Count Created Date = CALCULATE( COUNT (SUPPORT_INCIDENTS[CREATE_DATE]), USERELATIONSHIP(SUPPORT_INCIDENTS[CREATE_DATE],'Calendar'[Date]))

The field is in the field list, but I don't see the column in the table of data.  It doesn't let me add this field to the slicer.  

Why does this do a count?  I'm not clear how these two measures will allow me to report the Created and Closed dates on one line chart.

Again, sorry for being new at this.

[MFelix · Accepted solution]

Hi @mhambalek,

Below is a print screen with how you should setup your measures and graphs, also an image of the relationship window.

As you can see in the RAW data table the calculations are being made for each of the open close dates based on the calendar table.

Regards,

MFelix

[mhambalek]

Hi @MFelix,

I've followed what you've said and understand it, but am still not getting the right data.  What fields are in the axis and values properties of your line chart?

[MFelix]

Hi @mhambalek,

Axis - Date - Calendar table (hierachy - month)

Values - Measures created for open / close (made with userrelationship)

Regards,

MFelix

[mhambalek]

That's what I have and nothing gets graphed in the chart.  I can't choose the hierarchy for the Date field in the Axis.  So it must be how my calendar table is setup.

[MFelix]

Do you have the relatoonship between the tables?

[mhambalek]

I deleted the relationships and recreated them.  Both are bidirectional.  I don't know why the from and to are switched between the two.

[MFelix]

Hi @mhambalek,

Confirm if all your columns are formatted as date, In my tests I can't replicate the same error.

Is it possible to have a sample of your pbix file?

Regards,

MFelix

[mhambalek]

I can't share the pbix because of the data that's in it.  I suppose I could save a copy and strip out the sensitive data.

Here's what I've noticed:

- All three fields (Calendar Date, Create_Date, Date_Closed) are formatted as the same Date type

- The Create_Date line in the chart reports the correct data, but Date_Closed does not.  The data points in the chart doesn't reflect what is in the table.

- The Create_Date allows me to select a hierarchy if I put it into the Axis propertly, but the Calendar Date and Date_Closed does not.  Why wouldn't Calendar Date have a hierarchy when I put it into the Axis property?

- The default relationship for the Create_Date and Date_Closed were different (see screenshot above).  What should they be?

Thanks again for your time.

[MFelix]

Hi @mhambalek,

Taking into account that I don't have the file you can try and check if on both relations you have one (calendar table) to many (Support incident) relation if not force them in this way so you would have

Calendar Date - Create Date

Type: One - Many

Calendar Date - Closed Date

Type: One - Many

Regards,

Mfelix

[mhambalek]

Hi @MFelix,

I'm stumped.  I've matched the relationships as the two of you have described, yet the chart shows no data.  After creating a raw data table, I see that the Count measure for Closed Date isn't working because it's show a '1' even though no date exists for it.  I'm attaching my pbix in hopes you can help me figure this out.  

Many thanks.

[mhambalek]

I finally figured it out.  I had formatted my date fields from date/time to date in the table/data view, but not in the query. Once I transformed the date within the query the graphs worked. Lesson learned.

[MFelix]

On the slicer and x axis of the line chart you.must use the date on the calendar date the measures are to.use in the values of the line chart, you want to count the number of open and closed cases right?

Mfelix