Forum Discussion
Creating a slicer which works with two date tables
Super Usernicksearle1966 , 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
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