Forum Discussion
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:
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
- amitchandakSuper User
- Ashish_MathurSuper User
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.