[SOLVED] PowerBI: Find all records valid on specific date

PowerBI: Find all records valid on specific date

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?

Solution

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
    )