cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Creating a slicer which works with two date tables

Hi

 

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.

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Creating a slicer which works with two date tables

@nicksearle1966 

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.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

Highlighted
Super User IV
Super User IV

Re: Creating a slicer which works with two date tables

@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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

Check out my webinar : https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Regular Visitor

Re: Creating a slicer which works with two date tables

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

Highlighted
Community Support
Community Support

Re: Creating a slicer which works with two date tables

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

Allan

 

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

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.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors