Forum Discussion
Get earliest date when condition fulfilled
assuming following tables structure
Order
| Order | Quantity | DeliveryDate |
| X | 6000 | 01/08/2018 |
| Y | 500 | 02/07/2018 |
Production
| Order | ProductionDate | Quantity |
| X | 25/07/2018 | 2000 |
| X | 26/07/2018 | 3000 |
| X | 02/08/2018 | 1000 |
| X | 03/08/2018 | 200 |
| Y | 03/06/2018 | 300 |
| Y | 04/06/2018 | 300 |
| Y | 24/06/2018 | 300 |
this code will work as calculated column in Order
ProducedDate =
VAR RequestedQuantity = 'Order'[Quantity]
VAR AccumulatedProduction =
ADDCOLUMNS (
Production,
"ProducedQuantity", CALCULATE (
SUM ( Production[Quantity] ),
FILTER (
Production,
Production[Order] = EARLIER ( Production[Order] )
&& Production[ProductionDate] <= EARLIER ( Production[ProductionDate] )
)
)
)
VAR CompletedProduction =
FILTER ( AccumulatedProduction, [ProducedQuantity] >= RequestedQuantity )
RETURN
CALCULATE ( FIRSTDATE ( Production[ProductionDate] ), CompletedProduction )EDIT - I assume there is join between the tables on Order
Hi Stachu,
Thank you very much on your prompt reply.
Unfortunately this yield to me error of "there is not enough memory to complete the operation" so I cannot use this solution...
I think I need to provide some more info:
1. the table of production has many many records (it is large table...)
2. the relation between orders to production is one to many (many production records related to a single order)
3. I already have the following 2 measures:
- At Production table I have
Cumulative Production = CALCULATE ( [Total Production] ,FILTER (ALLSELECTED( Production ),Production[Date] <= MAX ( Production[Date] )) )
- At Orders table I have
Order Cumulative Production = CALCULATE([Cumulative Production], FILTER(Production,Production[Date] <= LASTDATE(Orders[RequiredDate])))
Any other solution ?
Thanks in advance