Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shukshuk
Frequent Visitor

Get earliest date when condition fulfilled

Hi,

I have a technical problem that I cannot solve...

In my model I have two tables. one for Orders and one for Production records.

The table of orders contains, among other data, also the required quantity and the estimated date for shipment.

The table of production contains the production events in related to the different orders - quantities and their date.

For example I have order X for 6,000 units to be delivered at Aug 1, 2018 and at the Production table I can find the following related records for production:

  • 2,000 units at July 25
  • 3,000 units at July 26
  • 1,000 units at Aug 2.
  • 200 units at Aug 3 (an extra quantity). 

 

For each order I would like to get the date that all required quantity of the order as been manufactured.

This shall be the earliest date that the fulfill the condition that all required units were produced.

(earliest date since we might produce an extra units).

At the example mentioned above the expected date shall be Aug 2 

 

Any idea how it can be done ? by DAX or Power Query ? can it be a calculated column?

 

Thanks in advance.

 

2 REPLIES 2
Stachu
Community Champion
Community Champion

assuming following tables structure
Order

OrderQuantityDeliveryDate
X600001/08/2018
Y50002/07/2018

Production

OrderProductionDateQuantity
X25/07/20182000
X26/07/20183000
X02/08/20181000
X03/08/2018200
Y03/06/2018300
Y04/06/2018300
Y24/06/2018300

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 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.