PowerBI filter- selected Date between Start and End date

0
votes

I'm building a PowerBI report from a dataset that contains start and end dates. I'd like to filter the dataset based on rows that would encompass a selected date in another table.

The screenshot here shows a sample. I want to click a date in the table on the right and have the table on the left filtered where the selected date is between the start & end date.

Two datasets side-by-side. One has a Start and End date. The other is a list of dates.

I've attempted several different things using columns and measures, but I haven't been able to nail it down. I also attempted to create a new table from a DAX expression that references the selected date, but that caused errors.

How can I dynamically filter the dataset based on the selected date being between the start and end date values?

1
powerbi

1 Answers

1
votes

Create a measure to check whether a row overlaps the selected date range:

Date Included = 
IF (
    FIRSTNONBLANK ( Table1[Start Date], 1 ) <= MAX ( 'Calendar'[Date] ) &&
    FIRSTNONBLANK( Table1[End Date], 1 ) >= MIN ( 'Calendar'[Date] ),
    "Include",
    "Exclude"
)

Add this Measure as a filter on your visualisation, where Date Included is Include

Now you can filter your Calendar table ( to single value, or range), and only overlapping rows from your fact table will be displayed.

See https://pwrbi.com/so_55925954/ for worked example PBIX file