Creating a slicer which works with two date tables

[nicksearle1966]

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

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

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.

[v-alq-msft]

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.

[amitchandak]

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

[nicksearle1966]

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

I honestly think I am nearly there.......lol

[jdbuchanan71]

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