PowerBI: Find all records valid on specific date

Question

I have a classic datawarehouse with records. Each record has a valid_from and a valid_to date.

Now I want to set a filter in PowerBI. The idea is that the user somehow sets a date and all records for which this date falls between valid_from and a valid_to should be available in PowerBI. Thus granting the user the possibility of timetravelling through the data.

This seems like a very standard task but I can't find how to do it.... Suggestions?

Have you tried the [`FILTER` function](https://learn.microsoft.com/en-us/dax/filter-function-dax) with the conditions you want? — Alexis Olson, Jun 17 '19 at 15:54
Looks like it would do what I need. But how to implement this? — Henrov, Jun 19 '19 at 06:32
If you want more specific answers, you need to make your question more specific: provide data sample, post image of your data model, and describe an example of the desired outcome. Help us reproduce your setup and validate the results. — RADO, Jun 20 '19 at 20:41

score 2 · Accepted Answer · answered Jun 20 '19 at 22:02

Given the vague question without explicit details, I'll have to make some assumptions. I'll assume that you have a date slicer that populated from a parameter table unrelated to your data table and that you have a set of measures you use in your visual(s) to display the records you're interested in.

Given those assumptions, you can write a measure to filter your an existing measure along these lines:

FilteredMeasure = 
VAR SelectedDate = SELECTEDVALUE ( DateSlicer[Date] )
RETURN
    CALCULATE (
        [ExistingMeasure],
        FILTER (
            DataTable,
            DataTable[valid_from] <= SelectedDate
                && SelectedDate < DataTable[valid_to]
        )
    )

Here's another similar but not completely equivalent formulation:

FilteredMeasure = 
VAR SelectedDate = SELECTEDVALUE ( DateSlicer[Date] )
RETURN
    CALCULATE (
        [ExistingMeasure],
        DataTable[valid_from] <= SelectedDate,
        DataTable[valid_to] > SelectedDate
    )

PowerBI: Find all records valid on specific date

1 Answers1