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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors