Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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....
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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

     

       

AlB's avatar
AlB
Community Champion
7 years ago

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

 

   

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you AIB! Your solution works for us :)

  • dsi's avatar
    dsi
    Frequent Visitor
    6 years ago

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

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