LAST DATE FROM FILTERED DATA

[andrewhann]

I am having trouble creating a last date from filtered data from QA Test results so that I can compare dates and select results only from the last test done:

My date looks like this:

TRREF_TRV	TRDATE_TRV	QSCHARTCAT	TRVALUE	QASPEC_TRV	QSVER_TRV	TRPASS_TRV	Latest TRDATE_TRV
P/716211	12/07/2017 13:34	Q33	84.4	471808_QC	1	N	12/07/2017 13:34
P/716211	17/07/2017 03:09	Q33	82	471808_QC	1	Y	17/07/2017 03:09

I am filtering by QASPEC_TRV and QSCHARTCAT and end up with these two results. I want to see only the last test result (ie 17/07/2017) You can see that my attempt at latest date brings the last date for the row not for all the rows.

I want 17/07/2017 against both rows so that I can test if it is the last date and filter on that.

Any help appreciated.

[v-ljerr-msft]

Hi @andrewhann,

Based on my test, you should be able to use the formula below to create a new measure to calculate the selected Latest TRDATE_TRV in your scenario. 

Latest TRDATE_TRV = CALCULATE(MAX(Table1[TRDATE_TRV]),ALLSELECTED(Table1))

Remark: you will need to replace 'Table1' with your real table name.

Regards

[andrewhann]

Thank you for that - it works perfectly as requested.

One further extension required - I need the last date for each TRREF for multiple TRREFs:

TRREF	TRDATE	QSCHARTCAT	TRVALUE	QASPEC_TRV	Latest TRDATE_TRV
P/716202	08/08/2017 07:09	Q33	82.82	471808_QC	31/08/2017 10:35	08/08/2017 07:09
P/717351	04/08/2017 01:33	Q33	82.4	471808_QC	31/08/2017 10:35	04/08/2017 01:33
P/717352	04/08/2017 01:33	Q33	82.26	471808_QC	31/08/2017 10:35	04/08/2017 01:33
P/717353	07/08/2017 15:45	Q33	81.49	471808_QC	31/08/2017 10:35	07/08/2017 15:45
P/717354	04/08/2017 01:32	Q33	82.9	471808_QC	31/08/2017 10:35	04/08/2017 01:32
P/717356	01/08/2017 08:43	Q33	85.6	471808_QC	31/08/2017 10:35	08/08/2017 07:09
P/717356	08/08/2017 07:09	Q33	83.02	471808_QC	31/08/2017 10:35	08/08/2017 07:09
P/717357	01/08/2017 12:17	Q33	85.2	471808_QC	31/08/2017 10:35	04/08/2017 04:48
P/717357	04/08/2017 04:48	Q33	82.57	471808_QC	31/08/2017 10:35	04/08/2017 04:48
P/717859	01/08/2017 16:50	Q33	84	471808_QC	31/08/2017 10:35	08/08/2017 07:09
P/717859	08/08/2017 07:09	Q33	82.16	471808_QC	31/08/2017 10:35	08/08/2017 07:09
P/717860	02/08/2017 16:48	Q33	84.6	471808_QC	31/08/2017 10:35	08/08/2017 07:09
P/717860	08/08/2017 07:09	Q33	82.07	471808_QC	31/08/2017 10:35	08/08/2017 07:09

So that I can then filter for the last test for each TRREF where TRDATE = LatestTRDATE

Thank you in advance for your help.

[v-ljerr-msft · Accepted solution]

Hi @andrewhann,

The formula below should work in this new scenario. 

Latest TRDATE_TRV =
CALCULATE (
    MAX ( Table1[TRDATE_TRV] ),
    ALLEXCEPT ( Table1, Table1[TRREF_TRV], Table1[QASPEC_TRV], Table1[QSCHARTCAT] )
)

Regards

[andrewhann]

Perfect.

Thank you.