Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Track inventority with pivot table

Hello Everyone!

 

I have this large amount of data in wich I can see: clients and the poduct that this clients buy to my organization, I want to track my clients inventroy, my data show the week of the year, the material they buy, and a type of purchase, the purchase can be "Consignment" of "billing". The idea is that one client cound take a consignment of a 100 items and a billing of 50, so I need to know wich is the inventory for next week. 

Here is an example:

In the first week the client begin with 0 inventory, then the billing is of 200 and the consignment is of 750, so the result must be 0-200+750=550, and this is the incial inventory for the next week: 

 

This must be the result table: 

 

I´ve tried creating measures, and summerized tables but I dont know how to take the inventory for the next week, 

I have this measures: 

 

Cons =
CALCULATE (
    SUM ( Table_1[Cantidad] );
    FILTER ( Table_1[type] = "Consignment" )
)

 

Bill=
CALCULATE (
    SUM ( Table_1[Cantidad] );
    FILTER ( Table_1[type] = "Billing" )
)

 

 

Then I use: 

Dif =
CALCULATE (
    SUM ( Table_1[Cantidad] );
    FILTER ( Table_1[type] = "Consignment" )
)-
CALCULATE (
    SUM ( Table_1[Cantidad] );
    FILTER ( Table_1[type] = "Billing" )
)

But i dont know how to take the last value to the next period

Please I wound appreciate if someone can help me!

Need Help
Show and Tell
Tutorial Requests
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    Create a calculate column Initial:

     

    Initial = 
VAR Current_billing = CALCULATE(SUM(Table1[quantity]), FILTER(ALLEXCEPT(Table1, Table1[week]), Table1[type] = "billing"))
VAR Current_consignment = CALCULATE(SUM(Table1[quantity]), FILTER(ALLEXCEPT(Table1, Table1[week]), Table1[type] = "consignment"))
RETURN
Current_consignment - Current_billing

    Then create a calculate column Mid Step:

    Mid Step = 
VAR Current_Week = Table1[week]
VAR Previous_Week = CALCULATE(MAX(Table1[week]), FILTER(Table1, Table1[week] = Current_Week - 1))
VAR Current_Initial = CALCULATE(MAX(Table1[Initial]), FILTER(Table1, Table1[week] = Previous_Week))
VAR Current_billing = CALCULATE(MAX(Table1[quantity]), FILTER(Table1, Table1[type] = "billing" && Table1[week] = Current_Week))
VAR Current_consignment = CALCULATE(MAX(Table1[quantity]), FILTER(Table1, Table1[type] = "consignment" && Table1[week] = Current_Week))
RETURN
Current_Initial - Current_billing + Current_consignment

    Finally create a measure:

    Final = 
VAR Current_Week = MAX(Table1[week])
VAR Previous_Week = Current_Week - 1
VAR Current_Initial_Value = CALCULATE(MAX(Table1[Initial]), FILTER(ALL(Table1), Table1[week] = Current_Week))
VAR Previous_Mid_Step_Value = CALCULATE(MAX(Table1[Mid Step]), FILTER(ALL(Table1), Table1[week] = Previous_Week))
RETURN
Current_Initial_Value + Previous_Mid_Step_Value

    The result is like below:

     

     

    Community Support Team _ Jimmy Tao

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

     

     

     

     

     

3 Replies

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

    Anonymous ,

     

    Create a calculate column Initial:

     

    Initial = 
VAR Current_billing = CALCULATE(SUM(Table1[quantity]), FILTER(ALLEXCEPT(Table1, Table1[week]), Table1[type] = "billing"))
VAR Current_consignment = CALCULATE(SUM(Table1[quantity]), FILTER(ALLEXCEPT(Table1, Table1[week]), Table1[type] = "consignment"))
RETURN
Current_consignment - Current_billing

    Then create a calculate column Mid Step:

    Mid Step = 
VAR Current_Week = Table1[week]
VAR Previous_Week = CALCULATE(MAX(Table1[week]), FILTER(Table1, Table1[week] = Current_Week - 1))
VAR Current_Initial = CALCULATE(MAX(Table1[Initial]), FILTER(Table1, Table1[week] = Previous_Week))
VAR Current_billing = CALCULATE(MAX(Table1[quantity]), FILTER(Table1, Table1[type] = "billing" && Table1[week] = Current_Week))
VAR Current_consignment = CALCULATE(MAX(Table1[quantity]), FILTER(Table1, Table1[type] = "consignment" && Table1[week] = Current_Week))
RETURN
Current_Initial - Current_billing + Current_consignment

    Finally create a measure:

    Final = 
VAR Current_Week = MAX(Table1[week])
VAR Previous_Week = Current_Week - 1
VAR Current_Initial_Value = CALCULATE(MAX(Table1[Initial]), FILTER(ALL(Table1), Table1[week] = Current_Week))
VAR Previous_Mid_Step_Value = CALCULATE(MAX(Table1[Mid Step]), FILTER(ALL(Table1), Table1[week] = Previous_Week))
RETURN
Current_Initial_Value + Previous_Mid_Step_Value

    The result is like below:

     

     

    Community Support Team _ Jimmy Tao

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

     

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hello v-yuta-msft 

     

    I've tried the columns and measure with my data and didn't worked, I think is because I have other clients and other materials per clients. 

    Also I want to see in the result table the initial, billing, consignment and final value for each week

    Do you know how can I take into account this? more clients and materials?

     

    I would like to share the data with you in the same report 

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

      Anonymous ,

       

      If the logic you have posted, I haven't found any relation between "diff" and "Material", "Clients", could you clarify about the logic above? In addtion, could you share some sample data and give the expected result?

       

      Community Support Team _ Jimmy Tao

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