Sales Cost Changes

[Daviejoe]

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

[parry2k · Accepted solution]

@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 apply

and 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.⚡

[amitchandak · Accepted solution]

@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])))

[parry2k · Accepted solution]

@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 apply

and 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]

Thanks to you both @parry2k and @amitchandak 

It works perfectly, thanks, much appreciated.

David