Running Total per product Return Purchase Date

[Anonymous]

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.  

Hope my explanations was clear enough. 

[Icey · Accepted 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.

[Anonymous]

@Icey 

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

[Icey]

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.

[Icey]

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.

[Anonymous]

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.