Forum Discussion
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 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
5 Replies
Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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.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
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))
