Power BI DAX: Return the Latest Record Before a Selected Date
Question details
The user needs a DAX result that returns the most recent record for each Dataset Id whose CreatedOn date is on or before a selected date.
- Product
- Microsoft Power BI DAX
- Device & OS
- not provided
- Scenario
- A Power BI model contains multiple records per Dataset Id and must identify the latest qualifying record based on a report-selected date.
- Observed behavior
- The required result must exclude records after the selected date and older qualifying records, while retaining the record with the greatest CreatedOn date for each dataset.
Verify that CreatedOn is stored as a true Date or Date/Time column rather than text, and confirm that a marked date table is related to the model. Decide whether the result must respond to a slicer; measures are dynamic, while calculated tables are evaluated during model refresh.
Create a dynamic DAX measure for the latest record before the selected date
Use this approach when the result must change immediately as users select different dates in a report slicer.
A measure can use a virtual filtered table and TOPN to select the most recent qualifying row. The example below assumes the report visual provides one Dataset Id per row and DataId is a unique tie-breaker.
If CreatedOn is still text, create a date column first with DATEVALUE or change the column data type in Power Query. A measure returns a scalar value, not a standalone table of rows.
In Power BI Desktop, open Modeling > New column and create a converted date column such as CreatedOnDate = DATEVALUE('Source'[CreatedOn]). If the text format is not recognized, use Home > Transform data and set the CreatedOn type to Date in Power Query.
Create or use a calendar table, mark it as a date table through Table tools > Mark as date table, and place its Date column in the slicer. The measure can use SELECTEDVALUE('Date'[Date]) to read the current selection.
Open Modeling > New measure and enter: First Record Before Selected Date = VAR CutoffDate = SELECTEDVALUE('Date'[Date], MAX('Date'[Date])) VAR CurrentDataset = SELECTEDVALUE('Source'[Dataset Id]) VAR Candidates = FILTER(ALL('Source'), 'Source'[Dataset Id] = CurrentDataset && 'Source'[CreatedOnDate] <= CutoffDate) VAR LatestRow = TOPN(1, Candidates, 'Source'[CreatedOnDate], DESC, 'Source'[DataId], DESC) RETURN CONCATENATEX(LatestRow, 'Source'[DataId], ", ").
Insert a table or matrix visual, add Source[Dataset Id] as the row field, and add the new measure as a value. Change the Date slicer to verify that the returned DataId changes and that records after the selected date are excluded.
Build a refresh-time calculated table for a fixed cutoff date
Use this option when the cutoff date is fixed at refresh time and the output must be a physical table that can be related to other model objects.
Work with Imported Data and Date-Based Results in WPS Office
WPS Office does not execute Power BI DAX measures or interactive Power BI slicers, but it provides a free, lightweight way to inspect, filter, sort, and summarize exported Excel or CSV data. Its familiar spreadsheet interface supports migration from Microsoft Office files while keeping common formats compatible.
- 1. Open the exported dataset: In WPS Spreadsheet, select Menu > Open and choose the exported .xlsx or .csv file.
- 2. Convert and filter the date field: Select the CreatedOn column, use Data > Text to Columns or Format Cells to convert recognizable text into dates, then apply Data > Filter with a condition for dates on or before the cutoff.
- 3. Sort and review the latest row: Use Data > Sort to sort by Dataset Id and CreatedOn in descending order, then review the first qualifying record within each dataset.
Frequently Asked Questions
Can a Power BI calculated table respond to a date slicer?
No. Calculated tables are evaluated during data refresh rather than when a user changes a slicer. Use a measure with SELECTEDVALUE, FILTER, and TOPN for an interactive result.
How do I convert a text CreatedOn value into a DAX date?
Create a calculated column from Modeling > New column with DATEVALUE('Source'[CreatedOn]), or open Home > Transform data and change the column type to Date in Power Query.
How does TOPN choose one record when two rows have the same CreatedOn date?
Add a second descending sort expression, such as DataId, to TOPN. This provides a deterministic tie-breaker and ensures only one row is returned.
What should I use if no date is selected in the slicer?
Use a fallback in SELECTEDVALUE, such as MAX('Date'[Date]), or return BLANK when no single date is selected. The correct choice depends on whether the report should use the latest available date or require an explicit selection.
Can a DAX measure return a complete table of matching records?
No. A measure returns a scalar value. Use CONCATENATEX to display selected values as text, or create a calculated table or Power Query output when a physical table of rows is required.
