Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

show date if falling between a start and an end date.

I have a data set with a few thousand line items. In this, there are 2 columns, a start date, and an end date. I am looking for a way to determine if an item was standing during a certain date. This is easy to do with a simple if statement, but the problem Im having is that I want to be able to filter it at the visual level by any date, selected by the user. 
I have [ActualBuildDate] and [ActualDismantleDate] Where [ActualBuildDate] will aways have a date, and [ActualDismantleDate] will alway be after [ActualBuildDate] or blank (Still standing). 


If I select January 2020, I want to see every item that was standing during January 2020.


I have a Calender table set up using this code 


Date Filter =
IF(MAX('1_Overview'[ActualDismantleDate]) > MAX('1_Overview'[ActualBuildDate]), MAX('1_Overview'[ActualDismantleDate]), MAX('1_Overview'[ActualBuildDate])
I have set a filter with a date heiarchy using this. but filtering by the dates on it do not return the proper items. 


Super User
Super User

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

Tag       start date     end date  standing

510/5/2020 standing
610/6/2020 standing
1011/8/2020 standing


This would be a simplified version of the data. 

Calendar table is separate and is the range of dates from earlest to latest in the previous table (10/1/2020 to 11/9/2020, whether the latest date is in the start or end column.)



Helpful resources


2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors