Reference Data Given Filters Max Date & Date Less than Other Date

[Anonymous]

Hello, I have a large dataset of Items purchased by Date, and Item Costing Implementation by Date. I am having trouble pulling the cost at the time of purchase into the purchase table. My datasets are below. I also have a Date Table as a bridge.

Item	Date Purchased	Qty Purchased		Item	Date of Cost Implementation	Item Cost
1	1/1/2019	20		1	5/4/2017	10
1	3/4/2019	100		1	6/9/2018	11
1	7/3/2019	65		1	4/23/2019	12
2	2/5/2019	40		2	12/9/2018	20
2	6/16/2019	75		2	3/18/2019	21
2	8/23/2019	35		3	4/23/2019	30
3	3/4/2019	110		3	9/2/2019	31
3	8/4/2019	20
3	12/5/2019	65

Could someone please help me with the logic on how to pull the cost (at the time of purchase) into the purchase table in the "Cost of Item" column?

Below is my desired output for Cost of Item in this sample set. I have also provided a link to a pbix file for this.

Item	Date Purchased	Qty Purchased	Cost of Item
1	1/1/2019	20	$11
1	3/4/2019	100	$11
1	7/3/2019	65	$12
2	2/5/2019	40	$20
2	6/16/2019	75	$21
2	8/23/2019	35	$21
3	3/4/2019	110	$30
3	8/4/2019	20	$30

Example File 

[Greg_Deckler · Accepted solution]

Perhaps:

Cost of Item = 
  VAR __ItemCostDate =
    MAXX(
      FILTER('CostTable',[Date of Cost Implmementation] <= [Date Purchased] && 'CostTable'[Item] = 'PurchaseTable'[Item]),
      [Date of Cost Implementation]
    )
RETURN
  MAXX(
    FILTER('CostTable',[Date of Cost Implementation] = __ItemCostDate && 'CostTable'[Item]='PurchaseTable'[Item]),
    [Item Cost]
  )

[Greg_Deckler · Accepted solution]

Perhaps:

Cost of Item = 
  VAR __ItemCostDate =
    MAXX(
      FILTER('CostTable',[Date of Cost Implmementation] <= [Date Purchased] && 'CostTable'[Item] = 'PurchaseTable'[Item]),
      [Date of Cost Implementation]
    )
RETURN
  MAXX(
    FILTER('CostTable',[Date of Cost Implementation] = __ItemCostDate && 'CostTable'[Item]='PurchaseTable'[Item]),
    [Item Cost]
  )

[Anonymous]

@Greg_Deckler 
Thanks for the quick response & solution!!!

[Anonymous]

@Greg_Deckler 

I'm sad to re-post in this, but I have an issue. This calculation takes FOREVER to run with my data. Currently, I am asking to reference a table with 2.9M rows & pull data from that to a table with 94k rows. Unfortunately, I need to run this for 5 columns. 

Do I need to just accept that these will take all day, or is there a better way using either Measures or Edit Queries?

Ryan

[Greg_Deckler]

Well, DAX optimization is a long and complex subject. I'd have to think about whether this can be done more efficiently. Would protentially help to move it to Power Query, perhaps @ImkeF  has some suggestions there.

[ImkeF · Accepted solution]

Hi @Anonymous  

this might be a faster operation in the query editor:

let
    Source = CostTable & PurchaseTable,
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Cost of Item", "Cost of Item - Copy"),
    #"Sorted Rows" = Table.Sort(#"Duplicated Column",{{"Item", Order.Ascending}, {"Date Purchased", Order.Ascending}, {"Cost of Item", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Cost of Item"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([#"Cost of Item - Copy"] = null))
in
    #"Filtered Rows"

[Anonymous]

@ImkeF @Greg_Deckler 

Thanks so much to each of you. One question. 

Is there a way to write DAX code for multiple columns or measures before actually processing the code? ie, I could create each column at end of day before leaving work & having it run overnight.

Thanks!

Ryan

[Greg_Deckler]

In thinking about this, I really think this comes down to your data model in terms of the speed of processing. If you could create a table that filled in all of the dates in your Cost table for your products that would speed things up tremendously because then you could create a relationship or use LOOKUPVALUE to grab your cost and avoid the messiness of table scanning with iteration functions like MAXX, etc.