Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter Data based on Date ranges

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 
        
    )

 

2 Replies