filtering records on three dates

Hi nick9one1 ,

To filter and display only the latest record that falls within the selected timeframe in your Power BI report, you need to create a measure that considers the financial quarter filter while selecting the most recent record. Since you have an active relationship between PlannedStartDateKey and the Date table and inactive relationships for RecordStart and RecordEnd, the approach involves using DAX to determine the most recent RecordStart date within the selected quarter.

First, create a measure that identifies the latest record within the selected financial quarter:

Latest_Record_Flag = 
VAR SelectedStartDate = MIN('dim_date'[Date])
VAR SelectedEndDate = MAX('dim_date'[Date])

RETURN 
CALCULATE(
    MAX(SLA[RecordStart]), 
    SLA[RecordStart] >= SelectedStartDate,
    SLA[RecordStart] <= SelectedEndDate
)

Next, create another measure to filter only the latest record:

Show_Latest_Record = 
IF(
    SLA[RecordStart] = [Latest_Record_Flag], 
    1, 
    0
)

Apply this measure as a visual-level filter in your table, setting it to "is equal to 1" to ensure only the most recent record matching the selected Financial Quarter is displayed. This approach dynamically adjusts the output based on the selected date range, ensuring that historical records do not appear unless they are the most recent within the timeframe.

Best regards,

Hi,

try to do the following steps 

1. Create a Financial Date Table

CalendarTable = 
VAR StartDate = DATE(2023, 9, 1)  -- Start of Financial Year 2024
VAR EndDate   = DATE(2026, 8, 31)  -- Extend for multiple years

RETURN
ADDCOLUMNS (
    CALENDAR ( StartDate, EndDate ), 
    "Financial Year", 
        "FY" & FORMAT(YEAR([Date]) + IF(MONTH([Date]) >= 9, 1, 0), "0000") & 
        "-" & FORMAT(YEAR([Date]) + IF(MONTH([Date]) >= 9, 1, 0) + 1, "0000"),

    "Financial Quarter", 
        SWITCH( TRUE(),
            MONTH([Date]) IN {9,10,11}, "Q1",
            MONTH([Date]) IN {12,1,2},  "Q2",
            MONTH([Date]) IN {3,4,5},   "Q3",
            MONTH([Date]) IN {6,7,8},   "Q4"
        )
)

2.Connect Orders[RecordStart] → CalendarTable[Date] (Many-to-One, Single Direction).

3. you can use date table finacial year & Quarter as slicer to filter

i got the following result in the test i did 

Hi nick9one1,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

Hi nick9one1,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

Hi nick9one1,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar