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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
labuser1235
Helper IV
Helper IV

Time taken to reach expected results

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 IDCompany NameTotal Order Received Till dateExpected orders till dateOver or under orders received till date
100Company ABC1020-10
101Company DEF20155
102Company GHI1520-5

 

 

TableB   
CompanyIDCompany NameDateOders Received
100Company ABCMonday, January 3, 20221
101Company DEFWednesday, January 5, 20222
100Company ABCWednesday, January 5, 20222
102Company GHIFriday, January 7, 20222
100Company ABCMonday, April 4, 20227
101Company DEFTuesday, April 12, 202218
102Company GHIMonday, April 18, 202213

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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]
        )
    )

1.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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]
        )
    )

1.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

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

Appreciate your Kudos.

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors