Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cross Filtering calendar table

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 displa...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

v-lili6-msft's avatar
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

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-lili6-msft
        Community Support
        7 years ago

        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