Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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?  

  • DataInsights's avatar
    DataInsights
    6 years ago

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