Forum Discussion
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
- BetterCallFrankResolver IV
Can you please upload a sample PBIX file somewhere? If you dont want to share actual data please provide dummy data.
Frank
- codyraptorResolver I
I've created a sample pbix file. Where would you like for me to load it? Thanks!!
- kcantorCommunity Champion
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.