Forum Discussion
Helper IILAST DATE FROM FILTERED DATA
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.
Hi andrewhann,
The formula below should work in this new scenario. :smileyhappy:
Latest TRDATE_TRV = CALCULATE ( MAX ( Table1[TRDATE_TRV] ), ALLEXCEPT ( Table1, Table1[TRREF_TRV], Table1[QASPEC_TRV], Table1[QSCHARTCAT] ) )Regards
5 Replies
v-ljerr-msftMicrosoft Employee
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. :smileyhappy:
Latest TRDATE_TRV = CALCULATE(MAX(Table1[TRDATE_TRV]),ALLSELECTED(Table1))
Remark: you will need to replace 'Table1' with your real table name.
Regards
andrewhannThank 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
Hi andrewhann,
The formula below should work in this new scenario. :smileyhappy:
Latest TRDATE_TRV = CALCULATE ( MAX ( Table1[TRDATE_TRV] ), ALLEXCEPT ( Table1, Table1[TRREF_TRV], Table1[QASPEC_TRV], Table1[QSCHARTCAT] ) )Regards