Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pclarke
Frequent Visitor

Return order amount for latest occurrence of each unique value

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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]))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors