Forum Discussion
Calendar Slicer - Only Display Selectable Months with Data
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?
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.
9 Replies
- amitchandak
Super User
Anonymous , 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
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.
- DataInsights
Anonymous, 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.
- DataInsights
Anonymous,
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:
