Compare Dates between 2 unrelated tables

DAX DAX Calculations
1

I have two tables:

  1. Quotes table - Quote #, Line, Quote Date, Part #, Customer, Qty, Price

  2. 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 =

CALCULATE (

[Subset Booked $$ of Quote] ,

FILTER (

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 =

CALCULATE (

[Total Booked Resale $$],

FILTER (Bookings,

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.

Capture 45
Capture 451358×775 157 KB

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:

Capture 46
Capture 461352×771 160 KB

Sample LQT to POS Analysis.pbix (2.3 MB)

2

@Rose

Your Item & Customers End Group Unique Tables do not contain all the Part # and customer end group from Quotes & Bookings table. In short, the model is poor. The measure is working but when putting Customer End Groups & Items Name side by side it’s taking forever to show. I would suggest you to rectify your data and model and then used the following measure:

Required Measure =
VAR QuoteDate =
    SELECTEDVALUE ( Quotes[Formatted Load Date] )
VAR Result =
    CALCULATE (
        [Total Booked Resale $$],
        FILTER ( ALLSELECTED ( Dates ), Dates[PADDATE] > QuoteDate )
    )
RETURN
Result

1
11920×1080 176 KB

1 Like
3

Hi @Rose, did the response provided by @MudassirAli help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

4

Hi @Rose, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

5

Hi there,
I understand what you’re saying. I will have to get with the people who generate the data tables and let them know that these have to be corrected. Then I will try your solution.

I will reply again once I have a chance to clean this up.

Thanks for the suggestions! Fingers crossed!

Rose

1 Like