Cross Filtering calendar table

[Anonymous]

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]

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]

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

[Aruljoy]

Hi Afernado,

Can you please provide me the sample PBIX file which you have in the image. I need to genrate simillar report? 

Thanks,

Arul Arockiam

[v-lili6-msft]

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

[Anonymous]

Hi @v-lili6-msft,

Thanks for your comment. My Table1 (according to your model) has future dates. It's indeed a sales table and it has budget records that go till the end of the current fiscal year but I want to have dates only till the current date. And PowerBI does not let me do any visual level filters.
So still my filter visual is showing dates future dates. Any suggestions?

Thanks in advance.

[v-lili6-msft]

HI, @Anonymous

You can add a column to judge whether the date is before current date in date table

before current = IF('Date'[Date]<=TODAY(),"Y","N")

then drag it into page level filter to filter "Y"

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft,

Thanks for the swift reply. I just want to filter the filter visualization, not the entire page because it has calculation till the end of month or fiscal year. 

PowerBI doesn't allow adding any column to the visual level filters.

Hope this makes sense.

Thanks,

Anton

[v-lili6-msft]

hi, @Anonymous

It seems that your slicer is HierarchySlicer, so just do these as below:

Step1:

Add a Year Month Number column for both calendar table and data table

calendar table

Year Month Number = YEAR ( 'Date'[Date] ) * 100 + MONTH ( 'Date'[Date] ) 

data table

Year Month Number = YEAR ( Table1[Date] ) * 100 + MONTH ( Table1[Date] ) 

Step2:

Add a measure like below:

Measure = if(MAX('Date'[Year Month Number])>YEAR ( TODAY() ) * 100 + MONTH ( TODAY()) ||MAX('Date'[Year Month Number])<CALCULATE(MIN(Table1[Year Month Number]),ALL(Table1)),BLANK(),MAX('Date'[Date]))

Step3:

Drag the measure into Values field

Best Regards,

Lin

[Anonymous · Accepted solution]

@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

[ndonahue]

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.

[dedelman_clng]

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]

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. 

[dedelman_clng]

I'll preface by saying I'm not very familiar with the limitations of DirectQuery (which I am assuming this is? You are not actually pulling the calendar table into PowerBI?), so while I'm pretty sure this would work if you had the calendar table locally, no promises in a DirectQuery scenario.

In the model, set up the filter direction between Calendar and your Fact table as "both", and then use the date on the Fact table to populate the slicer.

Again, may not work in your scenario if you have no control over the local PBI model.

[Anonymous]

@dedelman_clng,

I'm using the import mode. And yes, the calendar table is being pulled into PowerBI. Making the filter direction to both doesn't work for me because my sales table has gaps between dates and without a conscious date column I am unable to use time intelligent functions. 

Thanks.

[Anonymous]

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.