Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Filter active records at a specific date

 Hello!   So the problem is: I have a classic dataset with datewarehouse historization; each record has a validFrom, validTo and an isActive flag. I want to see the records in a table, that was ac...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous,

1. Create a date table in Power BI Desktop following the guide in this blog. Please note that there is no relationship between the date table and your original table.

2. Create a measure using DAX below in your original table.

chkmeasure = IF(MAX(Table[vaildFrom])<=MAX('Date'[Date])&&MAX('Date'[Date])<=MAX(Table[validTo]),1,0)


3. Create a table visual as shown in the following screenshot, create slicer using date field in the Date table, drag the chkmeasure to visual level filter, and set its value to 1. And choose yes for isActive field.



Regards,
Lydia

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous,

     

    Hey Lydia,

     

    Thanks for your answer! However, this is not a correct way, to solve my problem, because with this solution, we can find those records, which was already active at the selected date, and STILL currently active records.

     

    The problem is, that I cant find those records, which not active anymore, but was active at a specific date in the past.

    When I select a specific date, I want all the records, which was active at that day (SELECTED DATE was between validTo and validFrom[For these records, the validity can be 0 [not valid anymore]]), and thoose ones, which still actives since then (SELECTED DATE was between validTo and validFrom[For these records, the validity is 1 [still valid]]).

     

    Do you see any way to solve this problem?

     

    Thanks,

    Matt