Forum Discussion
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 active at the selected date, so logically: I select a date on a slicer. The list has to contain all the records, where is true, that the selected date is greater than the record's validFrom and smaller than the validTo.
Is it a solveable problem in Power BI? If yes, can you tell ma how is this possible?
Thanks,
Matt
9 Replies
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,
LydiaAnonymous,
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
rajulshahResident Rockstar
Hello Anonymous,
Using the following DAX function, we can achieve what you want:
IsActive = IF(FIRSTDATE(ALLSELECTED('DTdates'[Date]))<=FIRSTDATE(Table1[ValidFrom]) && LASTDATE(ALLSELECTED('DTdates'[Date]))>=LASTDATE(Table1[ValidTo]) ,1,0)
Here is the required result:
Let me know if I am missing on anything.
Hello Rayulshah!
I'm really appreciate your answer! It seems, it would be perfect solution, however FIRSTDATE and LASTDATE functions are not supported in Direct Query mode.
Is there any possible workaround for this kind of problem?
Thanks,
Matt