Forum Discussion
Sales Cost Changes
Hi,
struggling to work out the best way to create a measure that will take into account my cost changing over time. I realise this is probably relatively simple.
| Cost Change Table | |
| Cost Change Date | Item 1 |
| 01/04/2019 | £22.45 |
| 01/04/2020 | £22.85 |
| Sales Fact Table | |
| Date | Item |
| 30/03/2020 | Item 1 |
| 31/03/2020 | Item 1 |
| 01/04/2020 | Item 1 |
| 02/04/2020 | Item 1 |
Daviejoe I believe you want to apply the cost based on the date to your fact table, Your cost change table need to unpivoted to achieve this.
- transform data
- select cost change date
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and applyand now you can easily add the measure to get the cost based on the date.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Daviejoe , first unpivot the cost table have cost on row. Then create a column like this in sales table
new column = minx(filter(CostChange, CostChange[item] =Sales [item] && CostChange[Date] <=Sales [Date]),lastnonblankvalue(CostChange[date],max(CostChange[cost])))
3 Replies
Daviejoe I believe you want to apply the cost based on the date to your fact table, Your cost change table need to unpivoted to achieve this.
- transform data
- select cost change date
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and applyand now you can easily add the measure to get the cost based on the date.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Daviejoe , first unpivot the cost table have cost on row. Then create a column like this in sales table
new column = minx(filter(CostChange, CostChange[item] =Sales [item] && CostChange[Date] <=Sales [Date]),lastnonblankvalue(CostChange[date],max(CostChange[cost])))
