Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I’am a starter with Power BI, and I’ve got a question. The data in my example is fictional.
In our company we make every 2 weeks a cost price calculation. This cost price calculation is the basis for the calculation of sales prices. In general the sales prices and the cost prices are fixed for a 2 week period. But if a recipe changes it could be possible to receive a new cost calculation, within the 2 week period. The Cost Price is active till the next activation date of that item number.
I need to match de cost price of the product with the delivery date of the transaction table.
This example look a lot like this blog post:
The only difference is that I don’t have [To Date]. Is it necessary to create in Query Editor a column [From Date] and [To Date]? If so, how do I have to do it.
Thank you very much,
Remon
Table Cost Price
Item Number | Cost Price | Activation date |
1000 | 31,98 | 18-2-2020 |
1000 | 32,17 | 2-3-2020 |
1000 | 31,16 | 9-3-2020 |
1000 | 34,00 | 11-3-2020 |
2000 | 31,98 | 18-2-2020 |
2000 | 32,25 | 9-3-2020 |
2000 | 32,50 | 11-3-2020 |
3000 | 31,11 | 18-2-2020 |
3000 | 31,15 | 2-3-2020 |
3000 | 31,25 | 9-3-2020 |
Fact Table Sales transactions
Delivery Date | Item Number | Sales Quantity |
18-02-2020 | 1000 | 10 |
19-02-2020 | 1000 | 10 |
20-02-2020 | 1000 | 10 |
09-03-2020 | 1000 | 10 |
10-03-2020 | 1000 | 10 |
18-02-2020 | 2000 | 10 |
19-02-2020 | 2000 | 10 |
11-03-2020 | 3000 | 10 |
12-03-2020 | 3000 | 10 |
13-03-2020 | 3000 | 10 |
Solved! Go to Solution.
You may try this:
Add an Index Column in the Cost Price table post sorting it on Item Number & Activation Date
Add two calculated columns
For Till Date:
Till Date =
VAR _TillDate =
LOOKUPVALUE(dtCostPrice[Activation date],dtCostPrice[Item Number],dtCostPrice[Item Number],dtCostPrice[Index],dtCostPrice[Index] + 1)
VAR _Result =
IF(ISBLANK(_TillDate),TODAY(),_TillDate)
RETURN
_Result
For Cost Price:
Cost Price =
CALCULATE(
MAX(dtCostPrice[Cost Price]),
FILTER(
dtCostPrice,
dtCostPrice[Activation date] <= dtSales[Delivery Date]
&& dtCostPrice[Till Date] >= dtSales[Delivery Date]
&& dtCostPrice[Item Number] = dtSales[Item Number]
)
)
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
https://www.vivran.in/
Connect on LinkedIn
Hello @RemonKissen ,
You can achieve this using Power Query as well.
I have used Merge Queries: First to get the latest date for each item and then to get the cost of the latest date
Result:
You may find the sample pbix file here
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
https://www.vivran.in/
Connect on LinkedIn
You may try this:
Add an Index Column in the Cost Price table post sorting it on Item Number & Activation Date
Add two calculated columns
For Till Date:
Till Date =
VAR _TillDate =
LOOKUPVALUE(dtCostPrice[Activation date],dtCostPrice[Item Number],dtCostPrice[Item Number],dtCostPrice[Index],dtCostPrice[Index] + 1)
VAR _Result =
IF(ISBLANK(_TillDate),TODAY(),_TillDate)
RETURN
_Result
For Cost Price:
Cost Price =
CALCULATE(
MAX(dtCostPrice[Cost Price]),
FILTER(
dtCostPrice,
dtCostPrice[Activation date] <= dtSales[Delivery Date]
&& dtCostPrice[Till Date] >= dtSales[Delivery Date]
&& dtCostPrice[Item Number] = dtSales[Item Number]
)
)
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
https://www.vivran.in/
Connect on LinkedIn
When adding the Till Date measure to the Cost Price table where the Item Number is alphanumeric and not just numeric, will the LOOKUPVALUE portion need to change?
Big thanks for this tutorial! (I use it and it works right to my needs)
There are just small logic mistake - red marked sign has to be without "egual" - because old pricelist ends just day prior the new.
or much clear: ad -1 when calculate "Till Date column:
Hi @vivran22 ,
How would this expression look like in power query m?
Till Date = VAR _TillDate = LOOKUPVALUE(dtCostPrice[Activation date],dtCostPrice[Item Number],dtCostPrice[Item Number],dtCostPrice[Index],dtCostPrice[Index] + 1) VAR _Result = IF(ISBLANK(_TillDate),TODAY(),_TillDate) RETURN _Result
thanks!
Hi @RemonKissen ,
What [from date] and [To date] did you want? If possible, could you please explain it in details? By the way, if possible, could you please inform me more detailed information(such as your expected output)?
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @dax Zoe Zhi,
Thank you for your quick response. I hope I make my example clearer, I my example I expect that:
Item Number 1000, Cost 31.98, Activation date 18-02-2020 is active until the next update. In my example this should be active till 01-03-2020. On 02-03-2020 a new cost price is active for Item Number 1000. I added an additional column, till which date the price is active. Do I have to add an additional column? If so how do I have to do it with the Query Editor.
If a sales delivery occurred for item number 1000 on for example date 19-02-2020 than the cost price should be visible in the fact table “New Column”.
Thank you,
Remon
Table Cost Price
Was Activation date | New Column Added | ||
ItemNumber | CostPrice | From date | To date |
1000 | 31,98 | 18-2-2020 | 1-3-2020 |
1000 | 32,17 | 2-3-2020 | 8-3-2020 |
1000 | 31,16 | 9-3-2020 | 10-3-2020 |
1000 | 34,00 | 11-3-2020 | Till next update… |
2000 | 31,98 | 18-2-2020 | 8-3-2020 |
2000 | 32,25 | 9-3-2020 | 10-3-2020 |
2000 | 32,50 | 11-3-2020 | Till next update… |
3000 | 31,11 | 18-2-2020 | 1-3-2020 |
3000 | 31,15 | 2-3-2020 | 8-3-2020 |
3000 | 31,25 | 9-3-2020 | Till next update… |
Fact Table Sales transactions
New Column | |||
Delivery date | Item Number | SalesQuantity | Cost Price |
18-02-2020 | 1000 | 10 | 31,98 |
19-02-2020 | 1000 | 10 | 31,98 |
20-02-2020 | 1000 | 10 | 31,98 |
09-03-2020 | 1000 | 10 | 31,16 |
10-03-2020 | 1000 | 10 | 31,16 |
18-02-2020 | 2000 | 10 | 31,98 |
19-02-2020 | 2000 | 10 | 31,98 |
11-03-2020 | 3000 | 10 | 31,25 |
12-03-2020 | 3000 | 10 | 31,25 |
13-03-2020 | 3000 | 10 | 31,25 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
62 | |
18 | |
16 | |
13 |