Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@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]))
@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]))
Thanks heaps @amitchandak, the second one was perfect as it retained the ability to use other filters still.
Thanks again!