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
rohit1991's avatar
rohit1991
Super User
1 year ago

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.