Forum Discussion

fitconsulting's avatar
fitconsulting
Frequent Visitor
7 years ago
Solved

VALORATED CONSUMPTION

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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