Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
MattScruggs
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 =
DATESBETWEEN(
'Date'[Date],
MIN('1_Overview'[ActualBuildDate]),
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. 

 

2 REPLIES 2
amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Tag       start date     end date  standing

110/1/202010/5/2020complete
210/2/202010/6/2020complete
310/3/202010/7/2020complete
410/4/202010/8/2020complete
510/5/2020 standing
610/6/2020 standing
711/5/202011/6/2020complete
811/6/202011/7/2020complete
911/7/202011/9/2020complete
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.)

 

@amitchandak 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.