Forum Discussion
Calendar Slicer - Only Display Selectable Months with Data
Anonymous, 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 DatesDistinctJoin 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.
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.
Super UserAnonymous,
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:
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
Anonymous, 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 DatesDistinctJoin 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.
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.