Calendar Slicer - Only Display Selectable Months with Data

[AndyMills]

Dear Group:

I tried to post this, but the website errored out, so if it is a duplicate, please accept my apologies.  

Please understand, I am new to Power BI and this is my first report, so if my question is simple, please know it isn't for me as I am brand new.  

I have a report with a table that is displaying all the data in my table.  I added a Slicer to my report and can successfully filter the data based on the selections of the slicer.  My problem though is the default "start / end" date for the slicer.  

In my Model, I have a "calendar control table" that is used to join a couple tables.  However, this calendar table has dates from 2018 - 2028, so the slicer defaults to 01/01/2018 - 12/31/2028.  However, I only want it to default to the dates available in our table, which is current month back 13 months.  How would one go about doing this?  

[DataInsights]

@AndyMills, this is the approach I use:

1. Create a calculated table that uses the min and max dates from the fact table.

Example: SalesDates = CALENDAR ( MIN ( Sales[OrderDate] ), MAX ( Sales[OrderDate] ) )

2. Join the calculated table to the fact table.

3. Create a date slicer using the calculated table.

[AndyMills]

I tried this, but I am only able to either get the Start Date or End Date of the slicer to default, but not both.  

[DataInsights]

@AndyMills,

Is your date slicer type "Between"? In the example below, my fact table has data from 1/1/20 - 1/4/20, and it defaults to this date range: 

[AndyMills]

That is correct.  My date slicer is using a "Between" as your screenshot shows.  I understand what you are saying, but I believe my problem is that my table needs to pull in data from two different tables based on a date range.  In order to do this, in my model, I have a "ControlTable_CalendarTable" that has the unique dates and is then joined to the two data tables based on that unique date.  My Date Slicer then is using the date from the ControlTable_CalendarTable so that I can then slice the data based on date, as if i used the date column from the Data tables, it would only slice the data from one table, but not both.  This ControlTable_CalendarTable is a standard table we are to use in our reports and it contains dates from 1/1/2018 - 12/31/2029, which is then what is displayed as the start / end dates for the Date Slicer.  If I could have all the data in one fact table, I believe it wouldn't be an issue and would work as you explain below, but because I need to pull in data from multiple tables and the join is via date, my control table shows all.  I believe I could create a different ControlTable_CalendarTable that has the values of the dates in my Fact table, but the directive from my managers are to use this CalendarTable.  Hopefully that makes sense?

[DataInsights · Accepted solution]

@AndyMills, try this:

Two fact tables:

Sales_US

Sales_Other

Create a calculated table:

SalesDates = 
VAR DatesUS =
    SELECTCOLUMNS ( Sales_US, "Date", Sales_US[Date] )
VAR DatesOther =
    SELECTCOLUMNS ( Sales_Other, "Date", Sales_Other[Date] )
VAR DatesAll =
    UNION ( DatesUS, DatesOther )
VAR DatesDistinct =
    DISTINCT ( DatesAll )
RETURN
    DatesDistinct

Join the calculated table to the date table:

Create matrix. The row field should be Date from the date table (not the calculated table).

Create slicer using the calculated table.

[AndyMills]

Thank you SO MUCH for the help.  That is exactly what I needed and would never have been able to figure that out on my own, but it is exactly what I needed.  I appreciate the help and time you took with me more than you know.  

[DataInsights]

Awesome! Best of luck.

--Aaron

[amitchandak]

@AndyMills , Create a measure from fact and use that filter in the slicer

count(fact[value]) // in viusal level use not blank

This is for 2 slicer will work for 1 too

https://www.youtube.com/watch?v=cyOquvfhzNM

[AndyMills]

Thanks for the help, but for some reason, that video didn't seem to work but probably just not doing it correctly.  

I created a measure called "MaxDate".  I pulled that measure to the "Add Data Fields Here" for the slicer, but then it wants me to select the dates, it doesn't use the Measure I pulled into the slicer.  If I manually pic the dates, my slicer's start / end date works great, but doesn't seem to allow me to pull in a measure and do this.