Historized dataset: picking reference date in DAX between valid_from and valid_to

[Anonymous]

Hi,

I bring historized data into Power BI in the following format:

Contract_ID	Valid_from	Valid_to
1	17.2.2019	29.3.2019
1	30.3.2019	31.12.9999
2	10.1.2019	31.12.9999

The end-user would like to have the ability to pick a reference date and have the report reflect the sitaution on the chosen reference date. The desired logic is the following: Valid_from <= Reference Date <= Valid_to

For example:

1. picking reference date 18.2.2019 would mean that the first and the third rows are valid
2. picking reference date 1.4 would mean that the second and third rows are valid

This could be done with a query parameter, but parameters are not acceptable as they can currently only be changed in Power BI Desktop (instead of Service / Report Server).

Is there a way to do this in DAX with a single date slicer without using query parameters?

Thanks for the help!

[AlB · Accepted solution]

Hi @Anonymous,

Try this, where Table1 is the table you show: 

1.  Place Table1[Contract_ID] in the rows of a table/matrix visual   

2.  Let's assume Slicer[Date] is the name of the slicer you are using.  Create this measure:

ShowMeasure =
IF (
    SELECTEDVALUE ( Slicer[Date] ) >= SELECTEDVALUE ( Table1[Valid_from] )
        && SELECTEDVALUE ( Slicer[Date] ) <= SELECTEDVALUE ( Table1[Valid_to] ),
    1
)

3. Place [ShowMeasure] in the visual level filters and choose Show if value is -> 1

[dsi]

Unfortunately this seems to work only with tables or a matrix.

Is there a way to do this on the page or report level?

[Anonymous]

Thank you AIB! Your solution works for us 🙂