I have a calendar table that goes from 1901 up to 2020 and another tables have data from 2018 to 2020. When I add a slicer, it shows dates from the entire period in the calendar table. How do I display only the dates of the period that I have data? Thanks in advance.

v-lili6-msft, Thanks for your comment. I found a much simpler solution. When you add a column to the values bucket, only the records with data in that column appears in the filter list. Thanks,Anton

Cross Filtering calendar table | Microsoft Fabric Community

15 Replies

ndonahue
Frequent Visitor
7 years ago
You could try creating a date table that uses your fact table to determine the min and max dates that it should show.

Something like Date=calendar(min(salesdate), max(salesdate))

This should create a date table with contiguous dates between your first and last sale.
- Anonymous
  Not applicable
  7 years ago
  Thanks for the reply but I need to use the Calendar table from the data warehouse. Besides my sales table has data till the end of the fiscal year and I want to show dates only till the current month.
- dedelman_clng
  Community Champion
  7 years ago
  CALENDARAUTO() does something similar. It looks at the rest of your model and builds a calendar with every year your data contains. So if your sales are May 2013 thru Sep 2018, CALENDARAUTO() builds a calendar table from 1/1/13 - 12/31/18.
  - Anonymous
    Not applicable
    7 years ago
    Thanks for the replt dedelman_clng. Due to many reasons I have to use the calendar table from my data warehouse. Calendarauto is not an option for me.
v-lili6-msft
Community Support
7 years ago
hi, Anonymous
When you drag date field and other fields into a visual, if the field is aggregated, it will show data only the date that has data.
and if it is not aggregated, you can add a measure like
Measure = CALCULATE(MAX(Table3[Material]))
then drag it into visual level filter and set filter blank
Result:
I have a calendar table that goes from 2015 up to 2018
and another table has data from 2016 to 2018
Before
It has all the date
After

Best Regards,
Lin
- Anonymous
  Not applicable
  7 years ago
  Hi v-lili6-msft,
  
  Thanks for your comment. Attached is an image of my report. My matrix table is already showing only the dates with data. And I have multiple aggregated columns in it.
  What I'm trying to achieve is to show only dates with data in my filter visualization which is on the left. Hope this makes sense.
  Please let me know if you need any further details.
  
  Thanks, Anton
  - v-lili6-msft
    Community Support
    7 years ago
    hi, Anonymous
    After my test, you can do these as below:
    Step1:
    Use RELATED Function to add a column in date table that Which dates have data.
    has data = RELATED(Table1[Date])
    Step2:
    Drag the new column has data into page level filter and filter blank value
    Result:
    
    Best Regards,
    Lin