Pulling Value from Another Table based on Two Date Fields

[Anonymous]

Hi,

I have two tables as part of a data model - one called 'pbi TablePriceDiscStaging' and another called 'SalesOrderLineV2Staging'.  These two tables are linked via a field called 'AccountItemUnit.'

On the pbi TablePriceDiscStaging table, there are fields for FROMDATE and TODATE.  These fields represent the active time that a Pricing Agreement is valid.  I need to pull the 'AMOUNT' field from the pbi TablePriceDiscStaging table when the ORDERCREATIONDATETIME field (formatted as date) from the SalesOrderLineV2Staging table falls within the dates between FROMDATE and TODATE fields.  Is this possible?  If so, is there DAX that will work to accomplish this?  

I have already attempted making the transformation in the Query Editor to make a list of dates between the FROMDATE/TODATE fields, and the program crashes each time.

Thanks in advance!

[DataZoe · Accepted solution]

@Anonymous 

You could try this measure:

Amount in Pricing Agreement Dates =
CALCULATE (
    SUM('pbi TablePriceDiscStaging'[Amount]),
    FILTER (
        'pbi TablePriceDiscStaging',
        'pbi TablePriceDiscStaging'[FROMDATE] <= MIN ( SalesOrderLineV2Staging[ORDERCREATIONDATETIME] )
            && 'pbi TablePriceDiscStaging'[TODATE] > MAX ( SalesOrderLineV2Staging[ORDERCREATIONDATETIME] )
    )
)

[Anonymous]

@DataZoe , thank you so much, this worked!