Forum Discussion

lexxonnet's avatar
lexxonnet
New Member
3 years ago

Display records effective on a specific date

I have an interesting conundrum, and I've tried a number of different approaches on the PowerBI forum and none really seem to help me crack this one.

 

I am trying to visualise a table, with a date filter that allows me to show which records are active on a specific date.

The table has the fields Effective From and Effective To which are date fields.

 

I've created a new date table, unlinked to the current one, as below:

The measure SelValue in that table is set to MAX(CountriesDatePicker[Date]), which in itself is set using a slicer as below: 

 

Now, the actual table uses a measure with the following formula to set if a record is active or inactive on the specific date:

 

Entry status = IF(Countries[Effective From] <= CountriesDatePicker[SelValue] && Countries[Effective To] >= CountriesDatePicker[SelValue] , "Active", "Inactive")
 
With the above - I'd expect this row (highlighted) to be listed as 'Active' when the selected value is in 2006, however, it is inexplicably inactive!

 

Would appreciate any insights! The filter only ever seems to work when I set the filter to the latest date (i.e., today).

 

2 Replies