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.
Hi,
As the titel suspects I am asking about the impossible, well I tried about everything in my power, but could not return the wanted data.
From our SQL I have exported three sheets.
OrderLines
OrderId;StartDate;ProdNr;QTY;ProdCat ;Purchase Date
1;22-7-2020;789;21;a;
1;22-7-2020;258;12;b;
1;22-7-2020;456;9;a;
2;5-8-2020;456;5;a;
2;5-8-2020;258;17;b;
3;26-7-2020;258;19;b;
3;26-7-2020;456;22;a;
3;26-7-2020;321;12;c;
ProdCat
ProdCat;Del time Days
a;20
b;12
c;14
Stock
ProdNR;Stock;From Supplier;Sum;To Customers;Available
789;18;5;23;21;2
258;22;5;27;48;-21
456;13;20;33;36;-3
321;12;0;12;12;0
I would like to return per OrderLine the date what the last date is to purchase the goods. Per category the delivery date differs.
For Article 258, Sum suffies for the demand in Order 1. In Order 2 we are 2 pieces short. Therefor we look at the OrderLine[StartDate] (5-8-2020) and deduct the Category[Del time Days] of the corresponding Category (b). This will result in 24-7-2020 as our last viable Purchase Date.
In a visual we would like to see a list of products with the first purchase date needed.
I looked a lot on this forum but could not get a setisfying answer unfortunately.
Solved! Go to Solution.
Hi @Anonymous ,
How about creating a measure like below?
Purchase Date Measure =
VAR Sum_ =
SUM ( Stock[Sum] )
VAR QTY_ =
SUM ( OrderLines[QTY] )
VAR StartDate_ =
MAX ( OrderLines[StartDate] )
VAR Stock_2 =
Sum_
- CALCULATE (
SUM ( OrderLines[QTY] ),
ALLSELECTED ( OrderLines[OrderId] ),
OrderLines[StartDate] < StartDate_
)
VAR DelDays =
MAX ( ProdCat[Del time Days] )
RETURN
IF ( Stock_2 < QTY_, StartDate_ - DelDays )
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
How about creating a measure like below?
Purchase Date Measure =
VAR Sum_ =
SUM ( Stock[Sum] )
VAR QTY_ =
SUM ( OrderLines[QTY] )
VAR StartDate_ =
MAX ( OrderLines[StartDate] )
VAR Stock_2 =
Sum_
- CALCULATE (
SUM ( OrderLines[QTY] ),
ALLSELECTED ( OrderLines[OrderId] ),
OrderLines[StartDate] < StartDate_
)
VAR DelDays =
MAX ( ProdCat[Del time Days] )
RETURN
IF ( Stock_2 < QTY_, StartDate_ - DelDays )
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Wow this is great already!
The only thing I now have is, if I make a Table visual of the Product numbers and the Purchase Date Measure I will get the first date the measure finds.
Could this as well be the earlies measure per article?
Cheers
Hi @Anonymous ,
Try to add one measure like so:
Measure = MINX ( ALLSELECTED ( OrderLines[OrderId] ), [Purchase Date Measure] )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
OrderLines
OrderId;StartDate;ProdNr;QTY;ProdCat ;Purchase Date
1;22-7-2020;789;21;a;
1;22-7-2020;258;12;b;
1;22-7-2020;456;9;a;
2;5-8-2020;456;5;a;
2;5-8-2020;258;17;b;
3;26-7-2020;258;19;b;
3;26-7-2020;456;22;a;
3;26-7-2020;321;12;c;
Is "Purchase Date" missing or just needed to calculated?
And why do you return "24-7-2020" in you example? How did it get?
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey ,
The purchase Date is what has to be calculated, so this can be resturned in a visual per product.
I now see 24-7-2020 is even incorrect, it should be 14-7-2020 for 'OrderLine'[OrderID] 3, because this [StartDate] is earlier.
OrderLines[OrderID] requests for the 1st order 12 pieces
'Stock'[sum] has 27 pieces, So this meets the demand.
For OrderLines[OrderID] 3rd order the demand is 19 pieces. So for order 1, we deduct 12 pieces of 'Stock'[Sum] =27-12=15. This is not enough for the demand of 19 pieces. Therfore we can caluate what the latest date is to start pruchasing.
For this product's category (b) we know what the delivery date is 'ProdCat' (12 days). Then we deduct this days from the 'OrderLines'[StartDate].
Please let me know if something is still unclear., or isf you have a solution of course.
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |