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

Anonymous
Not applicable

Return the date of a table when condition is hitted

Hi, I am new to PowerBi.

Previously I use excel formula to create a table to forecast the 50% & 75% receive rate by Match & Index.

I want to know how can I do the same in PowerBI.

I have a list of data like below

 SKUs Ordered Unit Est Receive Date A 10 7/10 A 10 7/20 B 80 7/25 B 10 7/30

I want to show the date in the table when receive rate hit 50% n 70%.

Result:

 SKUs Ordered Unit 50% date 70% date A 20 7/10 7/20 B 90 7/25 7/25
2 ACCEPTED SOLUTIONS
Super User

@Anonymous

pls see the attachment below

Proud to be a Super User!

Super User

@Anonymous

Use two measures:

``````50 % =
var __50 = SUM(Table1[Ordered Unit]) * .5
return
MINX(
FILTER(
SUMMARIZE( Table1 , Table1[SKUs] , Table1[Est Receive Date] ),

"Cumm" , CALCULATE( SUM(Table1[Ordered Unit]) , FILTER( ALLSELECTED(Table1[Est Receive Date]), Table1[Est Receive Date] <= EARLIER(Table1[Est Receive Date] )))
),
[Cumm] >= __50
),
)``````
``````70 % =
var __70 = SUM(Table1[Ordered Unit]) * .7
return
MINX(
FILTER(
SUMMARIZE( Table1 , Table1[SKUs] , Table1[Est Receive Date] ),

"Cumm" , CALCULATE( SUM(Table1[Ordered Unit]) , FILTER( ALLSELECTED(Table1[Est Receive Date]), Table1[Est Receive Date] <= EARLIER(Table1[Est Receive Date] )))
),
[Cumm] >= __70
),
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
2 REPLIES 2
Super User

@Anonymous

Use two measures:

``````50 % =
var __50 = SUM(Table1[Ordered Unit]) * .5
return
MINX(
FILTER(
SUMMARIZE( Table1 , Table1[SKUs] , Table1[Est Receive Date] ),

"Cumm" , CALCULATE( SUM(Table1[Ordered Unit]) , FILTER( ALLSELECTED(Table1[Est Receive Date]), Table1[Est Receive Date] <= EARLIER(Table1[Est Receive Date] )))
),
[Cumm] >= __50
),
)``````
``````70 % =
var __70 = SUM(Table1[Ordered Unit]) * .7
return
MINX(
FILTER(
SUMMARIZE( Table1 , Table1[SKUs] , Table1[Est Receive Date] ),

"Cumm" , CALCULATE( SUM(Table1[Ordered Unit]) , FILTER( ALLSELECTED(Table1[Est Receive Date]), Table1[Est Receive Date] <= EARLIER(Table1[Est Receive Date] )))
),
[Cumm] >= __70
),
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

@Anonymous

pls see the attachment below

Proud to be a Super User!

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

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

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors