Show values of a field by first and last date.

[James_Ma]

Hi, I am trying to show a field value (which is a score rating) for multiple appointments for the same case.

So for example contact A has 5 appointments logged for Case1 and the first appointment was on the 1/01/2021 and the last appointment was on hte 28/02/2021.

the field for the score rating sits within the appointment table and only 3 appointments have a score.

How can I upll the data to show the first appointment date (with a score) and he last appointment date (with a score)?

Help is always appreciated

[KNP]

Hi @James_Ma - If this is still an issue for you, can you please post some sample data? It should be relatively simple to solve.

[James_Ma]

Hi KNP,

Thanks for your response, in reality a DAX expression would be perfect, I have a small data file which has hte relevant fields and I just want the Meaasure to pull the first date that has a score and hte last date that has a score.

Date of Assessment	Case ID	Full Name	Assessments ID	Q1 score	Q2 score
07/06/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	02cd84aa-aec7-eb11-bacc-000d3a86ef21	17	7
30/04/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	0b13cdca-c8a9-eb11-9442-002248001f1f	19	21
04/06/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	0e3452b2-bebf-eb11-bacc-0022481a610c
13/08/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	3cb46205-23fc-eb11-94ef-0022481b495a	19	17
07/05/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	4c764874-6fae-eb11-8236-002248005e20	21	19
14/05/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	72a7768f-4daf-eb11-8236-0022480050a8
25/06/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	7d56baa3-95d5-eb11-bacb-0022481aaa55	20	17
18/06/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	c255e553-aec7-eb11-bacc-000d3a86ef21	17	7
28/05/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	d74e4662-66bc-eb11-bacc-0022481a6e41	17	7
21/05/2021 00:00	e0c41db5-1219-4794-ae4f-680f409e3a59	Contact 1	fb585a25-52b9-eb11-8236-00224800ee20	19	19

[KNP]

Hi @James_Ma,

Sorry I missed your reply.

If you're happy with a DAX expression then I suspect @Greg_Deckler's answer will work. If you still want Power Query then I'll have a look for you.

[v-kelly-msft · Accepted solution]

Hi  @James_Ma ,

Create 2 measures as below:

The first date =
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[Date of Assessment] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Case ID] = MAX ( 'Table'[Case ID] )
                && 'Table'[Full Name] = MAX ( 'Table'[Full Name] )
                && (
                    'Table'[Q1 score] <> BLANK ()
                        || 'Table'[Q2 score] <> BLANK ()
                )
        )
    )
RETURN
    _mindate

The last date =
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date of Assessment] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Case ID] = MAX ( 'Table'[Case ID] )
                && 'Table'[Full Name] = MAX ( 'Table'[Full Name] )
                && (
                    'Table'[Q1 score] <> BLANK ()
                        || 'Table'[Q2 score] <> BLANK ()
                )
        )
    )
RETURN
    _maxdate

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Greg_Deckler]

@James_Ma This is Lookup Min/Max: 

Lookup Max Simple = 
    VAR __Table = 'Table'
    VAR __Max = MAXX(__Table,[Date])
RETURN
    MAXX(FILTER(__Table,[Date] = __Max),[Column])


Lookup Min Simple = 
    VAR __Table = 'Table'
    VAR __Min = MINX(__Table,[Date])
RETURN
    MAXX(FILTER(__Table,[Date] = __Min),[Column])

Lookup Min/Max - Microsoft Power BI Community 

And now I just realized that you want Power Query and this is a DAX solution. Oh well, if you are able to do it in DAX this is how. No idea in Power Query.