cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jondurbridge Frequent Visitor
Frequent Visitor

Slicer for date across whole report when I have two date fields

Hi Everyone,

 

I have tried to come up with a solution, but have failed unfortunately.

I need a single slicer on my main page of my report, that will filter all pages based on this date.

The problem I have is that I have 2 dates in my data. Opened date and closed date (this is for tickets logged)

 

If I apply created date as the date for my slicer, then it will show all tickets opened in that month, but will only show tickets closed in that month which were also opened in that month.

 

So I may have tickets closed in July which were opened in march, april, may etc... but with the Slicer I have in place currently, it will only show me tickets that were closed, which were also opened in the same month.

 

I hope this makes some sense Smiley Happy

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Slicer for date across whole report when I have two date fields

Hi @jondurbridge,

You need to create a calendar table and make a relationship with both dates in your table be aware that one of the relationship be inactive and you need to make measures with USER RELATIONSHIP.

Then create your slicers based on the dimension table created with sinked slicers.

Regards,
MFelix


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

Proud to be a Datanaut!




9 REPLIES 9
Super User
Super User

Re: Slicer for date across whole report when I have two date fields

Hi @jondurbridge,

You need to create a calendar table and make a relationship with both dates in your table be aware that one of the relationship be inactive and you need to make measures with USER RELATIONSHIP.

Then create your slicers based on the dimension table created with sinked slicers.

Regards,
MFelix


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

Proud to be a Datanaut!




jondurbridge Frequent Visitor
Frequent Visitor

Re: Slicer for date across whole report when I have two date fields

Could you please explain how the measure I make will work inline with the sliced date?

 

Super User
Super User

Re: Slicer for date across whole report when I have two date fields

Hi @jondurbridge ,

 

Just look at a simple model:

ID           Start Date  End Date

1 01/01/18 05/06/18
2 01/04/18 01/06/18
3 05/03/18 06/04/18
4 09/04/18 30/10/18
5 05/07/18 08/08/18
6 20/01/18 15/02/18
7 12/11/18 20/11/18
8 15/07/18 04/09/18
9 05/06/18 03/10/18
10 05/05/18 20/09/18

 

I have created a calendar Table and make two relationships with the previous table:

1 to Many - Start Date (Active)

1 to Many - End Date (Inactive)

 

I also created 2 measures:

Start Count = COUNT(Table1[ID]) + 0

End Count = CALCULATE(COUNT(Table1[ID]); USERELATIONSHIP('Calendar'[Date];Table1[End Date]))+0

The first one since there is an active relationship between start date and calendar is direct, the second I need to force the use of the inactive relationship. The + 0 on the end of both measure is to get values instead of blanks in order to have all months shown on the chart otherwise the months wihout data wouldn't be showned.

 

Using the Calendar table as my slicer and my axis on a simple chart you can see the calculation are presented according to slicer selection:

 

slicer_dates.gif

 

 

You can then sinc the slicer on several pages.

 

Check the PBIX file attach.

 

Regards,

MFelix



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

Proud to be a Datanaut!




jondurbridge Frequent Visitor
Frequent Visitor

Re: Slicer for date across whole report when I have two date fields

Appreciate the help, but I just cant see how I would apply that to my problem.

I have a ticket, which has a created time and a completed time.

Any graph I use has to have created or completed time as the axis.

This means that if i use created time as the axis, it will only show me tickets created during that time, which have now been completed.

So if we take October, it will not show me all tickets completed in October, only those that were also created in October

Super User
Super User

Re: Slicer for date across whole report when I have two date fields

Hi @jondurbridge ,

 

That's the point of making the bridge table, you will not use the Start and Close date on your visuals you will use the Calendar table then with the measures you will fullfill your data.

 

So when you see a October chart you have the correct calculation for Start and Close dates.

 

If you want to have tickets open at a certain date then you need to do a different calculation, again this calculation will be based on the relationship between your tables.

 

Regards,

MFelix



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

Proud to be a Datanaut!




jondurbridge Frequent Visitor
Frequent Visitor

Re: Slicer for date across whole report when I have two date fields

The problem with your solution MFelix........you are clearly far brighter than me, and I'm struggling to get it to work Smiley Very Happy

I have follwed the advice, but when I create a new date table, set the relationships correctly, they do not appear to work.

 

So forget about the fact that I have create and complete.

I have created a new date table by using Date = CALENDARAUTO(6)

 

Once the relationship is made, the existing create date field is no longer seen as a date in powerbi.

I set the slicer to the new "Date" as created above, but it does not filter by month and year etc.. If I select all, it shows me all the data as one big sum, but even using drilldown, it does not go to a month by month level.

Super User
Super User

Re: Slicer for date across whole report when I have two date fields

Hi @jondurbridge ,

 

When you create the relationship, the "hierarchy" is showned in the calendar table.

 

As you can see below then you need to use the calendar table for your axis.

 

dates.png

Regards,

MFelix

 



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

Proud to be a Datanaut!




jondurbridge Frequent Visitor
Frequent Visitor

Re: Slicer for date across whole report when I have two date fields

This is exactly what I have done. No matter what I do, the new date table does not have any bearing on the data from the other table. The relationship is active, and at present im only trying to filter on one date (create time)

 

I have a table where there are thousands of dates (DATE) which has a relationship with (Create_Time)

When I perform any filter on the new (DATE) it does not find any data relating to those dates at all.

 

Am i missing something blindingly obvious?

Do the dates in my new date table have to mirror the dates in the original table? Or can PowerBI realise that 01/01/2019 11:31 should show in a filter for 01/01/2019 ...

 

Thanks again for the help

Super User
Super User

Re: Slicer for date across whole report when I have two date fields

Can you share a sample of your file?

Regards,
MFelix


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

Proud to be a Datanaut!