filter table between two values with date dimension

[nick9one1]

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 between record start and end is in the sliced date dimension.

how can I do this?

[v-karpurapud]

Hi @nick9one1 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @nick9one1 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @nick9one1 

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and give a kudos, as this helps other members in  community.

Thank You!

[rohit1991]

Hi @nick9one1 

You can filter your table by checking if the date from your fact table lies between the start and end date from your financial year dimension. One way is to create a calculated column like this:

IsInSelectedPeriod = 
VAR SelectedStart = SELECTEDVALUE('DateTable'[StartDate])
VAR SelectedEnd = SELECTEDVALUE('DateTable'[EndDate])
RETURN 
IF(
    YourFactTable[Date] >= SelectedStart && YourFactTable[Date] <= SelectedEnd,
    1,
    0
)

Then just filter your visuals or table where IsInSelectedPeriod = 1. This should give you the expected results.

[FarhanJeelani]

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]

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;

[bhanu_gautam]

@nick9one1 Add a calculated column to your backlog table to determine if the record falls within the selected financial year and quarter.

DAX
IsInSelectedPeriod =
VAR StartDate = [StartDate]
VAR EndDate = [EndDate]
VAR SelectedStartDate = CALCULATE(MIN('Date'[Date]), ALLSELECTED('Date'))
VAR SelectedEndDate = CALCULATE(MAX('Date'[Date]), ALLSELECTED('Date'))
RETURN
IF (
(StartDate <= SelectedEndDate) && (EndDate >= SelectedStartDate),
1,
0
)

Use this calculated column to filter your backlog table in your report. You can add a visual level filter or a page level filter to only show records where IsInSelectedPeriod is 1.

If you need to create measures for reporting purposes, you can use a similar logic in your measures. For example:

DAX
BacklogCount =
CALCULATE(
COUNTROWS('BacklogTable'),
FILTER(
'BacklogTable',
'BacklogTable'[IsInSelectedPeriod] = 1
)
)