Date query to list all rows prior to a selected date

[msaundersdw]

I have a data set that comprises a few fields shown below; essentially a Business Function Area can be 'scored' at any point in time and that score is simply appended to the list. The score can go up or down and there will be more score fields attributed later.

I'm trying to formulate a table that will show the latest score, up to and including the selected date.

I've tried all sorts of measures around the MAX of the dates but I am only able to return rows that have the same selected date.

It's almost there without any fancy DAX: 

But I can't seem to apply the rule of 'just the last instance of, based on the date'. I added a unique id compromising the formatted date and ID but I can't seem to filter on a MAX of that either.

Any help/pointers would be amazing!

[msaundersdw]

But I don't want to transform the data and lose the expectation of being able to drill down into a specific process and see the changes over time.

[msaundersdw]

Selecting a process that hadn't been changed in the date range here would show as a straight line. Consider this as an example data set - the list has 250 items, so quickly identifying trends (such as those low, unchanging scores) is a requirement.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[msaundersdw]

‎11-21-2023 12:27 PM contains the sample source, ‎

11-21-2023 12:29 PM and 1-21-2023 12:30 PM has the sample output.

[lbendlin]

Consider using a simpler graphical solution. Let users find their own insights.

[msaundersdw]

Whilst i see the simplicity (and ease to deliver) a surmised table, the explicit requirement is to be able to

1. see the data set as at a point in time (so results for dates on and up to a specific date in a single list) and
2. be able to visualise the date changing over time - ideally via some sort of slider (to quickly highlight trends etc.) ... so being able to do the above at a number of dates/ranges 

[msaundersdw]

[msaundersdw]

Below are the tables that would be derived from filtering on date or quarter. Will eventuall show the change in all values - overall score used as an example. The graphs are just for illustration.

USER SELECTS 01-Feb-2023
Business Function Area	Process Description	Review Date	z_Overall Score
Business Area 1	Process 36	01/02/2023	80%
Business Area 1	Process 21	01/02/2023	90%
Business Area 2	Process 157	01/02/2023	0%

USER SELECTS Q1-2023
Business Function Area	Process Description	Review Date	z_Overall Score
Business Area 1	Process 36	01/02/2023	80%
Business Area 1	Process 21	01/02/2023	90%
Business Area 2	Process 157	01/03/2023	30%

USER SELECTS Q2-2023
Business Function Area	Process Description	Review Date	z_Overall Score
Business Area 1	Process 36	01/02/2023	80%
Business Area 1	Process 21	01/02/2023	90%
Business Area 2	Process 157	01/05/2023	50%

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[msaundersdw]

Apologies for the tardy response but I'm back on this now!

Here's some data.

Source:

Process ID	Unique ID	Business Function Area	Process Description	Review Date	Staff aware and trained	Tested	Formally Adopted	Not deviated from approved	Correctly Secured	Quality Rating of Output	Quality Rating of Process	z_Overall Score	Audit Comments
36	2023020100036	Business Area 1	Process 36	01/02/2023	1	1	0	0	0	5	5	80%
21	2023020100021	Business Area 1	Process 21	01/02/2023	1	1	1	0	1	5	5	90%
157	2023020100157	Business Area 2	Process 157	01/02/2023	0	0	0	0	0	0	0	0%
157	2023030100157	Business Area 2	Process 157	01/03/2023	1	0	0	0	0	0	0	10%
157	2023040100157	Business Area 2	Process 157	01/04/2023	1	1	0	0	0	0	2	30%
157	2023050100157	Business Area 2	Process 157	01/05/2023	0	1	1	1	0	0	4	50%