Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Creating a slicer which works with two date tables



I have two tables - the first is a yearly sales rep target table and the second is an actual results table.  I am new to power bi and I want one 'date slicer to work with both tables.  The issue I have is that on both the target table and the sales data tabel, the month is repeated many times


Here is the relationships picture

Relationship Image.jpg


The second one is my attempt at creating the graph.  At the moment If I choose month only the target data will change


Graph Image.jpg


What I want to achieve say for April is when I choose it gives me both the target and the actual value achieved for any given month.  I would then also like to be able to click on a rep and it highlights both of them in targets and actual GP.


Many Thanks - I hope it kind of makes sense.

Community Support
Community Support

Hi, @nicksearle1966 


Generally, your data model will contain a calendar table. In fact, it is usually better to aggregate data by year and month using columns of a calendar table (containing one row for each day) instead of extracting the date parts from a single column of type date or datetime in calculated columns. Here are some reasons for this choice. You obtain a model wherein all date attributes are included in a separate table making it easier to browse data using a generic client, and you can use special DAX functions that perform time intelligence calculations. Moreover, most of the time intelligence functions require a separate Date table to work correctly.


For further information, you may refer the article to see if it helps.


Best Regards



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User IV
Super User IV

@nicksearle1966 , Create a date table using calendar and have required columns like Month, Qtr , year etc.


Assume sales actual is at the day level.  ANd target has month and year in it.


Create a new column in the target table

Date = Date([Year],[month no],1)


Join date from both the tables to Date table and use

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :


Check out my webinar :

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak  thanks for the reply.  I tried copying this 

Create a new column in the target table

Date = Date([Year],[month no],1)


But then I realised my target tabel has the following 4 columns so doesnt actual have a date


Month - Sales Rep - Target - Month Number


Therefore that new custom colun wont work as it doesnt recognise 'date'


I was trying to do something similar to your suggestion using 'month as I created month in both the data table and the date table and that seemed to work as it just gave me the target and the sales for the month highlighted.  However, when I click on an individual, it only highlights say the target graph and doesnt highlight them in the actual profit graph.  What I have managed to do is get the GP by Sales rep to work when clicking on an individual but I cant get the targets to GP one working when I click on an indivudal.  I have once again copied my mapping for you to look at...

Latest mapping.jpg


I honestly think I am nearly

Super User II
Super User II


You will need a master date table for your model.  Just like you have a table of sales people hooked to both of your fact tables you would use the master date table to filter both of the fact tables.  Take a look at this article about time intelligence functions, it goes through what is needed to set up the date table.

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors