filtering records on three dates

[nick9one1]

I have a table of records with three dates. 

This is one 'Order' with history over three time periods indicated by the record start and record end date. 

I have an active relationship between planned start date key and my date dimension table. 
there are two inactive relationships for the record dates

In my report, I want to only show the latest record that matches the date timeframe selected. How can I do this?

e.g. here I have seleted financial Q1 of 2024 (Sep 2024 - Nov 2024).
so only the third line should show 

[kushanNa]

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 

[DataNinja777]

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,

[nick9one1]

I've managed to get your metric to run, but it doesnt behave as expected. 

Here you can see two rows for the same order. the Latest_Record_Flag metric matches the record start date for both rows. 

[v-mengmli-msft]

Hi @nick9one1 ,

It seems that it doesn't clear the Row Context. Please try ALL().

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

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

Best regards,

Mengmeng Li