Help Please

[Anonymous]

Hi guys, I need  help to calculate column-"Available"

movement is calculated as " SOH-SO+PO"

however, if the results is negative, I need it to be added into last month untill it gets positive result.

Thank you

[selimovd]

Maybe you can post how the result should look like.

It's a little bit confusing with the description and 3 tables

[Anonymous]

Hi @v-frfei-msft,@selimovd  ,

Thanks for your reply. The highlighted column is the result that I am trying to get from Column B,C,D

For example for case2, June movement is calculated by SOH(1000)-SO(0)+PO(0)=1000

July movement is calculated by -SO(1500)+PO(2000)=500

August movement is calculated by -SO(250)+PO(0)=-250

Sep movement is calculated by -SO(300)+PO(1000)=700

Therefore, Available column I want to show

Sep:    700 (because is a positive amount, then no need to carry over to previous month)

Aug:   -250 ( a negative amount, then carry over this to previous month-July)

July:   -250+500=250 (positive amount, then no need to carry over to previous month)

June:  1000 (positive amount, then is no need to carry over)

Case2 result is:

Jun   1000

July   250

Aug   -250

Sep   700

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please check the following steps as below.

1,To create calculated columns as below.

mn = 
SWITCH ( Table2[MONTH], "JUNE", 6, "JULY", 7, "AUG", 8, "SEP", 9 )

Movementc = 
Table2[PO] + Table2[SOH] - Table2[SO]

TF = 
VAR mn = Table2[mn] + 1
VAR mo =
    CALCULATE ( MAX ( Table2[Movementc] ), FILTER ( Table2, Table2[mn] = mn ) )
RETURN
    IF ( 'Table2'[Movementc] < 0 || mo < 0, 1, 0 )

2. After that, we can create measures as below.

Movement1 = SUM(Table2[PO])+SUM(Table2[SOH])-SUM(Table2[SO])

Measure3 = 
VAR a =
    CALCULATE (
        SUMX ( Table2, [Movement1] ),
        FILTER ( ALL ( Table2 ), Table2[mn] >= MIN ( Table2[mn] ) && [Movement1] < 0 )
    )
VAR b =
    IF (
        ISBLANK ( a ),
        [Movement1],
        IF (
            MAX ( Table2[TF] ) = 1,
            CALCULATE (
                SUMX ( Table2, [Movement1] ),
                FILTER ( ALL ( Table2 ), Table2[mn] >= MIN ( Table2[mn] ) && Table2[TF] = 1 )
            )
        )
    )
RETURN
    IF ( ISBLANK ( b ), [Movement1], b )

[Anonymous]

hi @v-frfei-msft ,

Amazing! Thank you.

I created measures based on your solutions and I got below erros:

Do you know the reason?  Thank you so much in advance!

[v-frfei-msft]

Hi @Anonymous ,

Sorry I cannot get the logic of you data. Could you please share more details of that?

[Anonymous]

Hi @v-frfei-msft ,

Thanks for your reply. The highlighted column is the result that I am trying to get from Column B,C,D

For example for case1, June movement is calculated by SOH(1000)-SO(0)+PO(0)=1000

July movement is calculated by -SO(1500)+PO(2000)=500

August movement is calculated by -SO(250)+PO(0)=-250

Sep movement is calculated by -SO(300)+PO(0)=-300

Therefore, Available column I want to show

Sep:   -300 (because is a negative amount, then carry over this amount to previous month-Aug)

Aug:   -250+(-300)=-550 (still a negative amount, then carry over this to previous month-July)

July:   -550+500=-50 (still a negative amount, then carry over to June)

June:  -50+1000=950 (positive amount, then is no need to carry over)