Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filter Date Columns Based on Slicer selection

Hi there,   I would like to do something like the "before" option of a slider date slicer but on a single selection Date slicer. This should be done in a Direct Query connection.   I have a table...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , As long as they part of same date table, you can get a min and max date. Like if you select dec-2019 from Month of fate table you can can get 2020-12-01 and 2020-12-31 as dates and use them

example

Measure= 
var _max =maxx(allselected('Date'),[date])
var _min =minx(allselected('Date'),[date])
return
CALCULATE(Sum('Table'[Units]),filter(all('Date'),'Date'[Date]<=_max && 'Date'[Date]>=_min))

or

Measure= 
var _max =maxx(allselected('Date'),[date])
var _min =minx(allselected('Date'),[date])
return
CALCULATE(Sum('Table'[Units]),filter(all('Date'),'Date'[Date]<=_max ))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi amitchandak.

     

    From what I can understand, your answer gives me a way to filter the table with the goal of using it in a calculation.

     

    My question was if you could Visually filter the table, i.e. display only certain values for the user to see (without using it for anything else than display).

     

    As a data sample you can see the following:

    Table A

    Date_Name              Date_Field

    2019 December        12/31/2019

     

    Table B

    Date_B      

    11/31/2019

    12/20/2019

    12/31/2020

     

    After the "filtering", Table B would look like this (it excluded the row for 12/31/2020 which was a date after the Table A selection 12/31/2019):

    Date_B

    11/31/2019

    12/20/2019

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Anonymous , Not tested, but see if this can work

      measure =
var _date = except(all(TableB[Date]),allselected(TableA[Date]))
return
calculate(countrows(TableB), filter(TableB, TableB[Date] in _date))
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        This seems to be working for the example that I presented. Thank you.

         

        I oversimplified the problem, only mentioning the date columns. Would it be possible to apply a similar logic to 2 tables that have a different structure? (different set of columns, only the Date one being mutual).