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

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      DataInsights
      Icon for Super User rankSuper User
      6 years ago

      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: 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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?