Forum Discussion

codyraptor's avatar
codyraptor
Resolver I
9 years ago

Date calculation between 3 tables

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.  

 

4 Replies

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

    Frank

    • codyraptor's avatar
      codyraptor
      Resolver I

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