Carrying inventory levels over periods

[ars]

I have a data that is based on skus and periods. I have initial demand and inventory. I put these periods on columns to be able calculate realized demand for each period. 

For example, there are demands of 51592,19800, 0, 9200, 0 with initial inventory of 285039 units (don't see period 100, they are just outliers, no calculation is needed). My inventory obviously covers all these 5 months' demand.

In the first period, I make a substraction to see inventory on-hand level. This value (negative value means I have inventory) should be carried over next periods, but since there is no period 3 in the data, I miss the inventory value. Thus, it seems like in the graphs I have an unsatisfied demand for periyod 3 and 4.

My formula refers to periods (e.g.,if period=3 then calculate(x)). If any period is missing, the calculation fails.

After reaching correct values, I want to see the pure demands by summing the columns which refer to periods' real demand.

[Anonymous]

Hi @ars ,

I create a table as you mentioned.

Then I create a calculated column and here is the DAX code.

Rank = 
RANKX (
    FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) ),
    'Table'[Value],
    ,
    ASC,
    DENSE
)

Next I create another calculated column and get what you want.

Column = 
VAR A_Value =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Name] = "A"
                && 'Table'[SKU] = EARLIER ( 'Table'[SKU] )
                && 'Table'[Rank] = EARLIER ( 'Table'[Rank] )
        )
    )
VAR B_Value =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Name] = "B"
                && 'Table'[SKU] = EARLIER ( 'Table'[SKU] )
                && 'Table'[Rank] = EARLIER ( 'Table'[Rank] )
        )
    )
RETURN
    IF (
        'Table'[Name] = "A"
            || 'Table'[Name] = "B",
        IF ( A_Value <> BLANK () && B_Value <> BLANK (), A_Value + B_Value, 0 ),
        BLANK ()
    )

Best Regards

Yilong Zhou

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

[ars]

Thanks a lot for your help 🙂

However, I have thousands of names and skus, therefore your solution does not fit in. According to my formulation, I generated a new formula by combining both rank and period and after that I separately summed demand of 1st period, demand of 2nd period etc.. For example, since period=4 corresponds to any rank between 1 and 4, I should add them up in order to find 4th period's demand.

**("sıra" means rank, "xth periyot net" means 1st period realized demand)

Periyot-4 Net =

VAR s1=CALCULATE(SUM('AA'[1.Periyot Net]), 'AA'[Periyot]=4,AA[Sıra]=1,'AA'[1.Periyot Net]>0)

VAR s2=CALCULATE(SUM('AA'[2.Periyot Net]), 'AA'[Periyot]=4,AA[Sıra]=2,'AA'[2.Periyot Net]>0)

VAR s3=CALCULATE(SUM('AA'[3.Periyot Net]), 'AA'[Periyot]=4,AA[Sıra]=3,'AA'[3.Periyot Net]>0)

VAR s4=CALCULATE(SUM('AA'[4.Periyot Net]), 'AA'[Periyot]=4,AA[Sıra]=4,'AA'[4.Periyot Net]>0)

RETURN s1+s2+s3+s4

[ars]

Thank you so much for your response 🙂 Another question comes up:

How can I match the ranks? For instance, for one sku rank 4 corresponds to 4th month, for another sku rank 4 corresponds to 3rd month. How can I calculate the real demand for 4th month?

[danextian]

try ALL ( 'Table'[Period] ) instead of FILTER.

[ars]

Thanks a lot for your contribution 🙂

According to my formulation, I generated a new formula by combining both rank and period and after that I separately summed demand of 1st period, demand of 2nd period etc.. For example, since period=4 corresponds to any rank between 1 and 4, I should add them up to find 4th period's demand.

**("sıra" means rank, "xth periyot net" means 1st period realized demand)

Periyot-4 Net =

VAR s1=CALCULATE(SUM('AA'[1.Periyot Net]), 'AA'[Periyot]=4,AA[Sıra]=1,'AA'[1.Periyot Net]>0)

VAR s2=CALCULATE(SUM('AA'[2.Periyot Net]), 'AA'[Periyot]=4,AA[Sıra]=2,'AA'[2.Periyot Net]>0)

VAR s3=CALCULATE(SUM('AA'[3.Periyot Net]), 'AA'[Periyot]=4,AA[Sıra]=3,'AA'[3.Periyot Net]>0)

VAR s4=CALCULATE(SUM('AA'[4.Periyot Net]), 'AA'[Periyot]=4,AA[Sıra]=4,'AA'[4.Periyot Net]>0)

RETURN s1+s2+s3+s4

[danextian]

Hi @ars ,

You can RANKX your period and perform calculatioins on the RANKX column instead:

There is no period 3 for Category A but the rank column shows 3 for Period 4.

Calcuation is performed on the RANKX column to get the previous period's value.