VALORATED CONSUMPTION

[fitconsulting]

I’m having trouble getting a query, could you please help me?

I have one first table with monthly stock consumption per product (CONSUMPTION TABLE).

I also have another table with monthly purchases as the following. In this case I got the last monthly purchase (PURCHASES TABLE).

Finally, I would need to get a table in which I have monthly products with their respective costs and consumption, and this is where I am having the most trouble (VALORATED CONSUMPTION)

CONSUMPTION
Month	Article	Stock Consumption
January	A	10
February	B	8
February	A	30
February	B	20
March	A	40
March	B	30
April	A	25
April	B	6

PURCHASES
Month	Article	Cost
January	A	-2
January	B	-3
February	A	-5
March	B	-8
April	A	-10

VALORATED CONSUMPTION TABLE
Month	Article	Cost	Unitary Cost	Valorated Consumption
January	A	10	-2	-20
February	B	8	-3	-24
February	A	30	-5	-150
February	B	20	-3	-60
March	A	40	-5	-200
March	B	30	-8	-240
April	A	25	-10	-250
April	B	6	-8	-48

[v-frfei-msft · Accepted solution]

Hi @fitconsulting,

One sample for your refernce.

1. Create two calculated columns in your tables.

Monthno = SWITCH('CONSUMPTION'[Month],"January",1,"February",2,"March",3,"April",4)

MonthNo = SWITCH(PURCHASES[Month],"January",1,"February",2,"March",3,"April",4)

2. Create the new calculated columns in CONSUMPTION table as below.

Unitary Cost = 
VAR _same =
    CALCULATE (
        SUM ( 'PURCHASES'[Cost] ),
        FILTER (
            PURCHASES,
            PURCHASES[Month] = 'CONSUMPTION'[Month]
                && PURCHASES[Article] = 'CONSUMPTION'[Article]
        )
    )
VAR _maxno =
    CALCULATE (
        MAX ( PURCHASES[MonthNo] ),
        FILTER (
            PURCHASES,
            'PURCHASES'[Article] = CONSUMPTION[Article]
                && 'PURCHASES'[MonthNo] < 'CONSUMPTION'[Monthno]
        )
    )
RETURN
    IF (
        ISBLANK ( _same ),
        CALCULATE (
            SUM ( PURCHASES[Cost] ),
            FILTER (
                PURCHASES,
                PURCHASES[Article] = CONSUMPTION[Article]
                    && 'PURCHASES'[MonthNo] = _maxno
            )
        ),
        _same
    )

Valorated Consumption = CONSUMPTION[Unitary Cost]*CONSUMPTION[Stock Consumption]

Then we can get the result as we need. Please find the pbix as attached for your reference.

 

Regards,

Frank