cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

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

Hi @mhambalek,

 

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

 

USERRELATION.png

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

19 REPLIES 19
Highlighted
Super User III
Super User III

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

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper I
Helper I

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

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.

Highlighted
Helper I
Helper I

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

@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.

Highlighted
Super User III
Super User III

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

Hi @mhambalek,

You need to do something like this

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

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper I
Helper I

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

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

Highlighted
Super User III
Super User III

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

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper I
Helper I

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

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.

Highlighted
Super User III
Super User III

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

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Super User III
Super User III

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

Hi @mhambalek,

 

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

 

USERRELATION.png

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors