Filter Data based on Date ranges

[Anonymous]

i have a table that  contains failure information recorded which includes  Date_Failure_End(which is the date the failure item was fixed) and an Date_Failure_Start(date when the failure item started) 

I also have a Date table that just has date items from 01-01-2006 till Current date.

Both of these tables are linked and the relationship is by the Date_Failure_Start from the Failure Table

i have a Date Slicer filter that i would like to use to filter the Failure Table to only show items where the date is in the range of start_date and end_date and for items that do not have end_dates(or end_date is Null) i would like the range to be based on the current_date 

i'm thinking that this could be solved by creating a measure that assigns the value "1" for each item if the slected date is in the range of the start_date and the end_date or in the event where there is no end_date the check is carried out if the selected date is in between the start_date and the  current_date. Finally assigns "0" to the other items that do not satisfy the condition

i'm not very good at DAX queries especially around nested if statements , i was wandering if anyone could help

Measure = 
VAR current_date = 
MIN( 'Date Table'[Date])
VAR start_date = 
MIN( 'NPTTest'[DATE_FAILURE_START])

VAR end_date = 
MIN( 'NPTTest'[DATE_FAILURE_END])

RETURN
    IF(
         (
            current_date >= start_date
        )
        && 
        (
            current_date <= end_date
         ),1,0 
        
    )

[Greg_Deckler · Accepted solution]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Also, you might find this article of interest:

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Anonymous]

This is great, it look like exactly what i need.

Thanks you so much