Display records effective on a specific date

[lexxonnet]

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).

[Ashish_Mathur]

Hi,

The ideal approach would be create one row for every data that lies betweenthe Effective From and Effective To dates.  The create a relationship (Many to One and Single) from the newly created date column to the date column of the Calendar Table.

[amitchandak]

@lexxonnet , It Should follow the HR Blog  logic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

file is attached