DAX Estimate stock plan if output more than input

[nontmn]

Hi,

I would like to create "Est-Stock" with "Measure" or "New calculate table" from other Measure 

It's ok in excel but not in power bi to sumup and use result to next step

Please Help.

All data is from measure.

https://1drv.ms/u/s!AqosNctB7VSa1C7F_Pmu-h2qHTrn?e=MkZc32

This is mock up file that similar original file

thank you

[nontmn]

https://1drv.ms/u/s!AqosNctB7VSa1C7F_Pmu-h2qHTrn?e=MkZc32

This is mock up file that similar original file

thank you

[v-yanjiang-msft]

Hi @nontmn ,

Sorry I'm not clear how Est-Stock is calculated, could you please explain more?

Best Regards,
Community Support Team _ kalyj

[nontmn]

[previous stock] = stock last period

Est-Stock = [previous stock] + [Plan-in] - [Plan-out]

but Est-Stock do not below zero

Thank you

[v-yanjiang-msft]

Hi @nontmn ,

Take the M0 for example, its calculation should be "stock(M-1)+Plan-In(M0)-Plan-Out(M0)", that is 150+100-50=200, but your result is 250. Also M15 is incorrect.

Could you please explain more.

Best Regards,
Community Support Team _ kalyj

[nontmn]

Sorry for my data M1 is mistake

[v-yanjiang-msft]

Hi @nontmn ,

According to your description, here's my solution.

1.Add an index column in Power Query.

2.Create a measure.

Est-Stock =
VAR _1 =
    IF (
        MAX ( 'Table'[Plan-In] ) = BLANK (),
        BLANK (),
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Index] <= MAX ( 'Table'[Index] )
                    && 'Table'[Plan-In] <> BLANK ()
            ),
            'Table'[Stock] + 'Table'[Plan-In] - 'Table'[Plan-Out]
        )
    )
VAR _2 =
    IF (
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Index] = MAX ( 'Table'[Index] ) - 1 ),
            _1
        ) < 0,
        'Table'[Stock] + MAX ( 'Table'[Plan-In] )
            - MAX ( 'Table'[Plan-Out] ),
        _1
    )
RETURN
    IF ( _2 < 0, 0, _2 )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[nontmn]

Sorry data not form only one table but from many table and calculate by measure 

I try to adapt your code but max can't use with measure and actually stock is sum form many SKU so index may not work

Thank you

[nontmn]

https://1drv.ms/u/s!AqosNctB7VSa1C7F_Pmu-h2qHTrn?e=MkZc32

This is mock up file that similar original file

thank you

[nontmn]

Please Help