Filter records with start dates before and end dates after a specific date based on 1 slicer value

[LaHaye]

I have a report with all sorts of different indicators, which for user-friendliness has just one slicer filter for the user to select the reporting year. All indicators come from different data sources and the semantic model is set up so that all year columns in the different data sources have a relation to a 'reporting year' column in a manually entered table. The 'reporting year' column from this table is used in the slicer, so that when you select 2023 for example, all the visuals for different indicators filter records with the value '2023' in their respective year columns in their respective source tables. Note that our reporting years last from the 1st of september to the 31st of august, so reporting year but we indicate our reporting years just by the calender year in which it starts. For example reporting year 2023 lasts from 1-9-2023 to 31-8-2024.

Now I want to add a new indicator, which poses a bit of a challenge, since the source table for this indicator doesn't have one year column but a 'project start date' and a 'project end date'. I want to make it so that when you select a year in the reporting year slicer, all records that were active for at least 1 day within that reporting year are filtered. For example, if you select '2023' in the reporting year slicer, all records with a start date in or before 2023 and an end date in or after 2023 should be filtered. Note that if projects are still ongoing, they have a null value for 'project end date', but they should be part of the selection if they started in or before 2023.

How can I make this work? 

[Sergii24]

Hi @LaHaye, what you can do is to write a measure "InScope" that satisfies your conditions. To test it, you can display 2 columns: project and the measure "InScope". The measure should filter the desired table in a way you want, after which you use a condition if countrows() > 0, then 1, else 0 (because measure can't return table). Once you're happy with the measure result (i.e. all projects you want to be visible have 1 in this test table), you can delete the table and insert the measure at visual level filter on all visuals you want and filter it there to the value 1 (measure can be inserted as a filter only at visual level filter, so you need to pereat it for each visual at your page).

Here is a simplified structure:

Measure1 = 
VAR _MinDate = some expression
VAR _MaxDate = some expression
VAR _Result =
   CALCULATETABLE(
      TABLE,
      DateTable[Date] >= _MinDate,
      DateTable[Date] <= _MaxDate
   )
RETURN
   IF(
      COUNTROWS( _Result ) > 0,
      1,
      0
   ) 

Try it out and let us know in case you stuck somewhere 😉