Forum Discussion

Sbudd's avatar
Sbudd
Icon for Helper II rankHelper II
4 years ago
Solved

Joining on date range

Hi   I have 2 tables which i would like to join if within a date range. Table 1 is a Orders,  Account name, date, units and value ordered. Plus a measure to calculate Rev. Table 2 is AM Table, s...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    As long as there is no overlap between different people for the same account then you can add a column to Sales as

    AM Name = 
var currentAccount = Sales[Account]
var currentDate = Sales[Date]
return CALCULATETABLE( SELECTCOLUMNS('AM Table', "AM Name", 'AM Table'[Name]),'AM Table'[Account] = currentAccount,
    'AM Table'[Start] <= currentDate && 'AM Table'[End] > currentDate )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

I think the Rev measure in the sample is incorrect, I include a corrected version below

Rev = SUMX(Sales, Sales[Units] * Sales[Value])

Rev by AM = SUMX('AM Table',
var startDate = 'AM Table'[Start]
var endDate = 'AM Table'[End]
var account = 'AM Table'[Account]
return CALCULATE( [Rev], Sales[Account] = account, Sales[Date] >= startDate && Sales[Date] <= endDate) )
Sbudd's avatar
Sbudd
Icon for Helper II rankHelper II
4 years ago

Thanks for that John, As my real life scenario is far more complex than the above, your solution works great for the simplified version but not fully. Is there a way of pulling in the Name into the Sales table so i can use the other dimensions/metrics?

I.e Is there a way of just adding the AM name to the sales table?

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    As long as there is no overlap between different people for the same account then you can add a column to Sales as

    AM Name = 
var currentAccount = Sales[Account]
var currentDate = Sales[Date]
return CALCULATETABLE( SELECTCOLUMNS('AM Table', "AM Name", 'AM Table'[Name]),'AM Table'[Account] = currentAccount,
    'AM Table'[Start] <= currentDate && 'AM Table'[End] > currentDate )