Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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]
)
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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]
)
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
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.
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |