Forum Discussion

nick9one1's avatar
nick9one1
Helper III
1 year ago

filter table between two values with date dimension

I have a backlog table with a start and end date. In my report I am slicing by a financial year and quarter e.g. 2024-2025 Q1.  I want to return all records in the backlog table where any date betwe...
Need Help
FarhanJeelani's avatar
FarhanJeelani
Super User
1 year ago

HI nick9one1 ,

Ensure Relationships:

There should not be a direct relationship between Backlog[RecordStart] or Backlog[RecordEnd] and dim_date[Date], as this won't work properly in this case.
Create a DAX Measure for Filtering:

Use a measure to check if any date between RecordStart and RecordEnd exists within the selected financial year and quarter.
DAX

Backlog Filtered =
VAR MinDate = MIN(dim_date[Date]) -- Get min date from slicer
VAR MaxDate = MAX(dim_date[Date]) -- Get max date from slicer

RETURN
IF(
MAX(Backlog[RecordStart]) <= MaxDate &&
MIN(Backlog[RecordEnd]) >= MinDate,
1,
0
)

Apply the Filter in a Visual:

Add this measure as a visual-level filter and set it to 1 to show only relevant records.


Alternative Approach (Calculated Column)
If you want to filter inside a table visual, create a calculated column:

DAX

Within Selected Period =
VAR MinDate = CALCULATE(MIN(dim_date[Date]), ALL(dim_date))
VAR MaxDate = CALCULATE(MAX(dim_date[Date]), ALL(dim_date))

RETURN
IF(
Backlog[RecordStart] <= MaxDate &&
Backlog[RecordEnd] >= MinDate,
"In Period",
"Out of Period"
)


Then, filter the table where Within Selected Period = "In Period".

 

Please mark this post as solution, if it hepls you. Appreciate Kudos.

 

  • nick9one1's avatar
    nick9one1
    Helper III
    1 year ago

    Thanks. This has almost worked. 

    In the table below I have added both the measure and the coluclated column.
    The measure looks like it works, but the caculated column doesnt. You can see it says 'in period' for a date that doesnt match the slicers above. 
    But the measure does seem to work So I will stick with that. 

     

    The only remaining issue is I cannot apply the visual filter to the bar chart. 

    I works on the table, but if I try to apply it to the bar chart there are no results;