Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mhambalek
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

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
MFelix
Super User
Super User

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



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.

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

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



What tables does that go in?  The support ticket table or calendar 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



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.

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



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?

Hi @mhambalek,

 

Axis - Date - Calendar table (hierachy - month)

Values - Measures created for open / close (made 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



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.

 

Image 2.jpg

Do you have the relatoonship between the tables?


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



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

 

Image 4.jpg

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


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



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.

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


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



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.

 

 

 

 

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.

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



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.