Add a 1 to each row that has latest date , so I can filter a report to show latest for each reg no

I have been using PBI for a few years , but I am now being asked to trial AWS Quicksight as a lot of our data is stored there , I am trying to replicate this query below , any help would be much appreciated , thanks…

Last Departure = IF (
‘TU Feed’[actual_departure]
= CALCULATE (
MAX ( ‘TU Feed’[actual_departure] ),
ALLEXCEPT (
‘TU Feed’,
‘TU Feed’[reg no.]
)
),
1
)

Hi @dev_opra -

Here is a way you can do this. Below I’ve included two examples, both use QuickSight’s Level Aware Aggregation.

With the first example you might have used already, it’s a window function similar to what you’d find in SQL. This finds the max over a subset of the dataset. You can also use this type of function with Rank over reg_no to flag your last row.

Thanks the rank formula worked perfect , my only issue now is that the count of movements is now showing 1 , is there a way to have count of reg , excluding latest departure , so if reg 0001A moved on
01/01/2022
01/02/2022
01/03/2022
Output required would be
Reg No . Latest Departure , Total Movements
0001A 01/03/2022 3

You can do that. You’ll use both examples above together, but instead of maxOver you’ll use countOver.

Thanks so much for your help on this , this has fixed the issue.

1 Like