Accumulative sum by Purchase Order and material - Power Query in Excel

Hi, I wanna create a Power Query with the accumlative sum of qty arrived by material and purchase order, and check the status (ok with an 85% of the qty ordered will be "ok") (0,85 its a parameter in query)

And I want to do a table with the first date with status ok , like the example. Please anybody can help me? Thanks

Hi,

I think i can solve this in the PowerPivot (not Power Query).  If you are OK with that, then share the link from where i can download your Excel workbook.

Hi DanielHidalgoS,

You may try  another option by use Dax in Power BI which should be similar with that in Excel.

You could creata one measure for acumulative sum , a calulated column for determin the Status for 85% and one calculated table for your desired.

1.measure for acumulative sum

accumlative sum =
CALCULATE (
    SUM ( 'Table1'[Qty arrive] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[Date] <= MAX ( 'Table1'[Date] )
            && 'Table1'[Materia] = MAX ( 'Table1'[Materia] )
            && 'Table1'[Purchase Order] = MAX ( 'Table1'[Purchase Order] )
    )
)

2.a calulated column for determin the Status for 85%

Stauts85% =
IF (
    DIVIDE ( [accumlative sum], 'Table1'[Quantity ordered] ) > 0.85,
    "ok",
    "Pending"
)

3. A calculated Table

Table =
SUMMARIZE (
    'Table1',
    Table1[Purchase Order],
    Table1[Materia],
    "First Date Status 85%", CALCULATE (
        MIN ( 'Table1'[Date] ),
        FILTER ( 'Table1', 'Table1'[Stauts85%] = "ok" )
    )
)

Then you will get the output. You also could have a look at the attatched file.

Hope this also can help you!

Best Regards,

Cherry

Hi DanielHidalgoS,

Have you solved your problem?

If you have solved, you could share your solution or always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry