Date calculation between 3 tables

[codyraptor]

I have a 'Dispatch' table that has a distinct count of dispatches.  It is connected to a 'Vendor' table with a distinct set of vendors.  The 'Vendor' table is connected to a 'Vendor Program' table which provides information related to their particular program.  I need the appropriate DAX language to either created a Flag in a calculated column...or just a measure.  Below is the logic:

Distinct count of dispatches (from dispatch table)

Vendor Program ID = 1240 (from vendor program table)

Dispatch Date (from dispatch date table) is between Vendor Program Effective Date & Expiration Date (from vendor program table)

OR

Dispatch Date (from dispatch date table) is >= Vendor Program Effective Date & Expiration Date is 'null' (from vendor program table)

So far...this is what I have...

=CALCULATE(Dispatch[Dispatch Count],FILTER(ALLSELECTED('Vendor Program'),'Vendor Program'[AHS_PROGRAM_ID]="1240"))

This works for the 1240 part of it....but I'm having issues with filtering the data for the date logic.  

[BetterCallFrank]

Can you please upload a sample PBIX file somewhere? If you dont want to share actual data please provide dummy data.

Frank

[codyraptor]

I've created a sample pbix file. Where would you like for me to load it?  Thanks!!

[v-caliao-msft]

Hi @codyraptor,

Please provide us some sample data and expected result, so that we can make further analysis. You could upload the sample file to onedrive and share it.

Regards,

Charlie Liao

[kcantor]

@codyraptor

Do you have a dedicated date table? Most date logic/time intelligence requires a dedicated date table with no missing dates in order for the dax to work appropriately.

See here for more information.

http://www.daxpatterns.com/time-patterns/