Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Stock by date

Hello, I am new to PowerBI and what I am trying to do (if it can be done in PBI), calculate the stock of parts according to the date they arrive until they are processed, as an example:

The pieces "A", "B" and "C" arrive on April 15 (The data appears in the column "Fecha_Llegada"), at the end the stock was in 3 pieces

On April 16, the piece "B" (Column "Fecha_Procesada" is processed, the final stock would be 2.

On the 17th, nothing is processed and the day's stock is 2

On the 18th "A" and "C" are processed and the piece "D" arrives, leaving the stock at 1

That's the idea, but to do it in a single graph and not separately

Kddk852_0-1650395985587.png

  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Syndicate_Admin  Kddk852 

    Thanks for reaching out to us.

    You can try the measure below

    Stock =
VAR _In =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[status] = "IN"
                && 'Table'[date] <= MIN ( 'Table'[date] )
        )
    )
VAR _processed =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[status] = "processed"
                && 'Table'[date] <= MIN ( 'Table'[date] )
        )
    )
RETURN
    _In - _processed

    result

    Kindly Note: in this sample, the quantity for each record is 1. Since not sure what dureza, impacto, tension are, I used status as column series instead.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi Syndicate_Admin  Kddk852 

    Thanks for reaching out to us.

    You can try the measure below

    Stock =
VAR _In =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[status] = "IN"
                && 'Table'[date] <= MIN ( 'Table'[date] )
        )
    )
VAR _processed =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[status] = "processed"
                && 'Table'[date] <= MIN ( 'Table'[date] )
        )
    )
RETURN
    _In - _processed

    result

    Kindly Note: in this sample, the quantity for each record is 1. Since not sure what dureza, impacto, tension are, I used status as column series instead.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Excellent, it has helped me a lot, just what I wanted, I will only adjust it to what I need, thank you very much!