Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Pulling Value from Another Table based on Two Date Fields

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!

Need Help
  • DataZoe's avatar
    DataZoe
    6 years ago

    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] )
    )
)

2 Replies

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      DataZoe , thank you so much, this worked!