Filter Date Columns Based on Slicer selection

[Anonymous]

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 that has a date fileld [Date_Field] and a [Date_Name] that stores the user friendly name of the date. The user would be able to select a Period (Here [Date_Name]), ex: 2019 December (the Date_Field corresponding to this will have the last day of December - 12/31/2019)

In a table, I would like to be able to only show records from a Table B where the data in a column [Date_B] is before the date corresponding to the selection (2019 December - 12/31/2019) 

Do you have any idea regarding this? 

Thank you

[amitchandak]

@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]

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]

@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]

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).

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.