Track inventority with pivot table

[Anonymous]

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!

[Anonymous]

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]

@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.

[v-yuta-msft · Accepted solution]

@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.