cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

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