Count rows of a table that exist in another table with date comparison

[rafiql09]

I want row count of ST_setups[PO_No_Key] table those also exists in inv_challan_gatepass.. table where for those PO_No_Key inv_challan_gatepass[shipment_date] is equal or earlier than ST_setups[delivery_date].  Date (ST_setups[delivery_date]) is selected from date slicer using DAX.

I just want to count PO_No_Key shipped on or before delivery date. Date is selected from slicer.

Thanks in advance.

[johnt75 · Accepted solution]

Try

Shipped on or before delivery =
COUNTROWS (
    FILTER (
        'inv_challan_gatepass',
        'inv_challan_gatepass'[shipment_date]
            <= RELATED ( ST_Setups[delivery_date] )
    )
)

[rafiql09]

Thanks Johnt75, it taises syntax error. Here is more sample data . Count is three here.

[johnt75]

What is the error it is raising ?

[rafiql09]

Why you write PO_No_Key column infront of the table name!!

[johnt75]

That's my copy & paste error. I've edited my original post to correct the mistake.

[rafiql09]

Here how are we checking PO_No_Key of ST_Setups table exist in inv_challan_gatepass table?

[johnt75]

The slicer on the delivery date will filter ST_Setups and that filter will be passed to inv_challan_gatepass by the relationship

[rafiql09]

I am not getting the right output. Is this relationship correct?

[johnt75]

Yes, that looks correct. Can you paste some sample data in a format that can be copied & pasted ?

[rafiql09]

https://docs.google.com/spreadsheets/d/1CqxEY2FOGOZvSsAI85ZxY445HEg1zOvR/edit?usp=sharing&ouid=10957...

[rafiql09]

This PO_No_Key 20409 is shpped before delivery date. but my report is not returnng.

[johnt75]

That works for me using the data you shared. Do you have any other filters being applied ?

Grab the query generated for the visual in Performance Analyzer and have a look at it in DAX Studio. That might give you some pointers.

[rafiql09]

Does it return count 1(one) if you select 30th august on your date slicer ? as 20409 its delivery date is 30th 2022 august and ship_date is 26th august 2022