Forum Discussion
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, showing whih Account manager was working on the account.
Account Name, AM Name, start date and end date.
Resultant table to be
AM name & Rev
I can join on Account name, but need to join on the dates aswell so its attributed to the correct AM.
I created a sample file here.... https://1drv.ms/u/s!Ao5kon8x9kjVjD23eSjJpIfgQXwx?e=LkiZl0
Much appreciate any help thanks!!
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 )
3 Replies
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) )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?
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 )