cancel
Showing results 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

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

1 ACCEPTED SOLUTION
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]
)
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
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]
)
)

Regards,

Xiaoxin Sheng

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

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

Helper IV

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.

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors