Return the date of a table when condition is hitted

[Anonymous]

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

[ryan_mayu · Accepted solution]

@Anonymous 

pls see the attachment below

[Fowmy · Accepted solution]

@Anonymous 

Use two measures:

50 % = 
var __50 = SUM(Table1[Ordered Unit]) * .5
return
    MINX(
        FILTER(
            ADDCOLUMNS(
                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
        ),
        Table1[Est Receive Date]
    )

70 % = 
var __70 = SUM(Table1[Ordered Unit]) * .7
return
    MINX(
        FILTER(
            ADDCOLUMNS(
                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
        ),
        Table1[Est Receive Date]
    )

[ryan_mayu · Accepted solution]

@Anonymous 

pls see the attachment below