topic Re: Return order amount for latest occurrence of each unique value in DAX Commands and Tips

Return order amount for latest occurrence of each unique value

pclarke — Tue, 15 Sep 2020 08:52:43 GMT

I have a database that is being written to daily which contains the as_at_date (the date the data is being written), the booking date and the amount for the booking. Once the booking has been finalised (which may occur a few days after the booking date itself) it will stop being written to the database so what i need is for it to show the amount when it is the last occurence of each unique booking_date, but if it is not, leave it blank.

Currently i the date looks like the below with as_at_date, booking_date and amount. I need to add the final_amount with the below results.

as_at_date	booking_date	amount	final_amount
12/09/2020	17/09/2020	80
13/09/2020	17/09/2020	90
14/09/2020	17/09/2020	110
15/09/2020	17/09/2020	110
16/09/2020	17/09/2020	100	100
14/09/2020	14/09/2020	220
15/09/2020	14/09/2020	280
16/09/2020	14/09/2020	200	200
12/09/2020	12/09/2020	150
13/09/2020	12/09/2020	150	150
16/09/2020	16/09/2020	250	250

Any help would be greatly appreciated.

Re: Return order amount for latest occurrence of each unique value

amitchandak — Tue, 15 Sep 2020 09:09:56 GMT

pclarke ,

Try a measure like

calculate(lastnonblankvalue(Table[as_at_date],sum(Table[amount])), allexcept(Table,Table[booking_date]))

or try

calculate(sum(Table[amount]),filter(Table, Table[as_at_date] =max(Table[as_at_date])) allexcept(Table,Table[booking_date]))

Re: Return order amount for latest occurrence of each unique value

pclarke — Wed, 16 Sep 2020 00:25:01 GMT

Thanks heaps amitchandak, the second one was perfect as it retained the ability to use other filters still.

Thanks again!