I have two tables:
Quotes table - Quote #, Line, Quote Date, Part #, Customer, Qty, Price
Bookings table - Customer, Part #, Booking Date, Qty, Price
I have a date table to which both dates are joined.
I’m trying to show which parts have been booked against the same part and the same customer in the Quote table, but ONLY if the Booking date is > than the Quote date.
Here’s the measure I created, which is not working (Measure 1):
Booked $ After Quote Period =
[Subset Booked $$ of Quote] ,
ALL ( Bookings ),
Bookings[Formatted POS Last Date] > max( Quotes[Formatted Load Date] )
where [Subset Booked $$ of Quote] is the total value of the booking where Booking[Part #] = Quote[Part #] and Booking[Customer] = Quote[Customer]. Here’s that measure (Measure 2):
Subset Booked $$ of Quote =
[Total Booked Resale $$],
contains( values( Bookings ) , Quotes[NIC PART NUMBER] , Bookings[Part #] , Quotes[End Customer Group], Bookings[End Customer Group])))
When I try to insert Measure 1 into my table, it errors or shows nothing. Basically, what I’m trying to do is achieve the same result if I were to use a date filter on the Booking Date.
Here’s what I’m getting now. The Booking Date is not being filtered so I’m getting ALL bookings regardless of when they were booked.
If I use a date filter for Booking date, I can get the results I need. But I need to incorporate this into my measures for Booking Qty and Booking Price:
Sample LQT to POS Analysis.pbix (2.3 MB)