cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

 

powerbidaxquery.PNG

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 
        
    )

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Filter Data based on Date ranges


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...


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: Filter Data based on Date ranges


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...


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Anonymous
Not applicable

Re: Filter Data based on Date ranges

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

 

Thanks you so much

 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors