Time taken to reach expected results

[labuser1235]

Hi All,

I have been trying to understand the time required to reach the target. 
When my expected orders till date is the current todays date.

Can someone please help me how should I calculate the time required to reach the target.

TableA
Company ID	Company Name	Total Order Received Till date	Expected orders till date	Over or under orders received till date
100	Company ABC	10	20	-10
101	Company DEF	20	15	5
102	Company GHI	15	20	-5

TableB
CompanyID	Company Name	Date	Oders Received
100	Company ABC	Monday, January 3, 2022	1
101	Company DEF	Wednesday, January 5, 2022	2
100	Company ABC	Wednesday, January 5, 2022	2
102	Company GHI	Friday, January 7, 2022	2
100	Company ABC	Monday, April 4, 2022	7
101	Company DEF	Tuesday, April 12, 2022	18
102	Company GHI	Monday, April 18, 2022	13

[Anonymous · Accepted solution]

HI @labuser1235,

You can try to use the following calculated column formula to lookup the table 2 records to get the first date that reaches or exceed the required amount.

Expected Date =
VAR summary =
    SUMMARIZE (
        FILTER ( Table2, [Company ID] = EARLIER ( 'Table'[Company ID] ) ),
        [CompanyID],
        [Date],
        "rolling",
            CALCULATE (
                SUM ( Table2[Oders Received] ),
                FILTER (
                    Table2,
                    [CompanyID] = EARLIER ( Table2[CompanyID] )
                        && [Date] <= EARLIER ( Table2[Date] )
                )
            )
    )
RETURN
    IF (
        [Over or under orders received till date] < 0,
        MINX (
            FILTER (
                summary,
                [rolling]
                    >= (
                        EARLIER ( 'Table'[Expected orders till date] )
                            - EARLIER ( 'Table'[Total Order Received Till date] )
                    )
            ),
            [Date]
        )
    )

Regards,

Xiaoxin Sheng

[amitchandak]

@labuser1235 The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[labuser1235]

Hi Amit Thanks for your response.
In the above table, For Example..
I expected to receive 20 orders by 18th April, for a company ABC. However, I have received only 10 orders until 18th April. I am still under 10 (20 expected - 10 Total orders received) orders from expected. 
I would like to know based on the dates I have received the orders howmany long days it might take to receive all the expected orders from that company.

I hope I was able to explain it in good terms. Thanks in advance.