Stop calculating cumulative due to change of condition

[DaurenA]

Hi Friends,

Can you please help to with code.

Need to stop calculating cumulative when it reaches 27 month age and the cumulative needs to remain same from 27 and beyond age categories.

[Dangar332 · Accepted solution]

hi, @DaurenA 

in above first you mention compare with age(when it reaches 27 month age and the cumulative needs to remain same from 27 and beyond age categories.)

so why you compare it with forcast daet .

above you sum for  'movement'[cumulative add col] and here sum with Movement[monthly cost]

try below 

Cumulative add col =
CALCULATE(
    SUM( Movement[monthly cost] ),
    FILTER(
        ALL( Movement ),
        Movement[Unique Ident #]
        = EARLIER( Movement[Unique Ident #] ) &&
        Movement[Forecast Day]
        <=EARLIER( Movement[Forecast Day] ) &&
         not(Movement[age]>=26)
   
    )
)

or try below also 

column =
     SUMX( 
       FILTER( 
          'movement',
          'movement'[unique ident #] = earlier('movement'[unique ident #]) &&
          'movement'[forcast day]<=EARLIER('movement'[forcast day]) && 
           not('movement'[age]>=26)
       ),
       'movement'[monthly cost]
     )

[Dangar332]

hi, @DaurenA 

try below code for column

column =
     SUMX( 
       FILTER( 
          'movement',
          'movement'[age]<=EARLIER('movement'[age]) && 
           not('movement'[age]>26)
       ),
       'movement'[cumulative add col]
     )

[DaurenA]

Hi @Dangar332 ,

Unfortunately it didn't work

This my code. May be needto modify it, so it stops cumulating after 26 age.

Cumulative add col =

CALCULATE(

    SUM( Movement[monthly cost] ),

    FILTER(

        ALL( Movement ),

        Movement[Unique Ident #]

        = EARLIER( Movement[Unique Ident #] )

        &&Movement[Forecast Day]

        <=EARLIER( Movement[Forecast Day] )

    )

)

[Dangar332 · Accepted solution]

hi, @DaurenA 

in above first you mention compare with age(when it reaches 27 month age and the cumulative needs to remain same from 27 and beyond age categories.)

so why you compare it with forcast daet .

above you sum for  'movement'[cumulative add col] and here sum with Movement[monthly cost]

try below 

Cumulative add col =
CALCULATE(
    SUM( Movement[monthly cost] ),
    FILTER(
        ALL( Movement ),
        Movement[Unique Ident #]
        = EARLIER( Movement[Unique Ident #] ) &&
        Movement[Forecast Day]
        <=EARLIER( Movement[Forecast Day] ) &&
         not(Movement[age]>=26)
   
    )
)

or try below also 

column =
     SUMX( 
       FILTER( 
          'movement',
          'movement'[unique ident #] = earlier('movement'[unique ident #]) &&
          'movement'[forcast day]<=EARLIER('movement'[forcast day]) && 
           not('movement'[age]>=26)
       ),
       'movement'[monthly cost]
     )

[DaurenA]

Hi @Dangar332,

Can we consider somehting else in the cumulative value? I should have addeed the beginning balance as of Jan 2024 and then also new purchase during the year. How can we implement this in the above cumulative?

Below is a sample from another table with initial cost and purchase cost. So the model should show whole capiatlized cost (beginning or purchase value plus monthly costs)

[DaurenA]

Hi @Dangar332 ,

Any idea how to amortize those latest cumulative reached at 26 age? I.e. 1026K should be amorized on monthly basis.

It should be amortized till cows get 80 months old. Remaining value value will be written off since cows expected useful life finisges at 80 months. 

[Dangar332]

hi, @DaurenA 

sorry but i don't know about amortize can you clear your requirnment.

you mention cows get 80 months old (is column present regarding to cow)?

[DaurenA]

Hi @Dangar332 

Please see below screen with comments.

How can I share with the model itself? Can it be attached here?

[Dangar332 · Accepted solution]

hi, @DaurenA 

@DaurenA 

there are some changes panding 

try below updated code

column =
 var a = SUMX( 
       FILTER( 
          'movement',
          'movement'[unique ident #] = earlier('movement'[unique ident #]) &&
          'movement'[forcast day]<=EARLIER('movement'[forcast day]) && 
           not('movement'[age]>=26)
       ),
       'movement'[monthly cost]
     )

var b = a*(1-(15/100))
var c = maxx(
           filter(
              'movement',
              'movement'[unique ident#]=earlier['movement'[unique ident#]]
               ),
           'movement'[age]
         )
var d = divide(b,c-27)
return
if('movement'[age]<27,a,d)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.and don't forgot to give kudos.

[DaurenA]

Hi @Dangar332 ,

Thank you very much! By the way how kudos are assigned?

That worked, however I did some little changes like below.

Impairment =

 var a = CALCULATE( MAXX(Movement, Movement[**bleep** till 27 age]))

var b = a * (1-(15/100))

var c = 53      (53 is 80-27 - the period for amortization)

var d = divide(b,c)

return

if('movement'[age]>26,d)

Now need your help to add initial investment to the first cumulative value so it is reflected further in the balance. Then impairment value will be autoadjusted considering initial investment.

[Dangar332]

hi, @DaurenA 

hi daurena you want add intial investment to column **bleep** till 27 age"  
if yes try below

column =
var a1 = sumx(filter(
              'movement',
              'movement'[unique ident#]=earlier['movement'[unique ident#]]
               ),
              'movement'[initial investment]
        ) +
       sumx(filter(
              'movement',
              'movement'[unique ident#]=earlier['movement'[unique ident#]] &&
              'movement'[age]=1
               ),
             'movement'[monthly cost]
        )

 var a = SUMX( 
       FILTER( 
          'movement',
          'movement'[unique ident #] = earlier('movement'[unique ident #]) &&
          'movement'[forcast day]<=EARLIER('movement'[forcast day]) && 
           not('movement'[age]>=26)
       ),
       if('movement'[age]=1 && ,a1 ,'movement'[monthly cost])
     )

var b = a*(1-(15/100))
var c = maxx(
           filter(
              'movement',
              'movement'[unique ident#]=earlier['movement'[unique ident#]]
               ),
           'movement'[age]
         )
var d = divide(b,c-27)
var a = sumx(filter(
              'movement',
              'movement'[unique ident#]=earlier['movement'[unique ident#]]
               ),
              'movement'[initial investment]
        )
return
if('movement'[age]<27,a,d)

[Dangar332]

hi, @DaurenA 

sorry for let response

you mention add initial investment to the first cumulative value so it is reflected further in the balance.  here first cumulative value means Monthly cost?

and can you elaborate below?

Then impairment value will be autoadjusted considering initial investment.

[DaurenA]

Hi @Dangar332

Monthly cost is used to calculate cumulative which is column "**bleep** till 27 age" . I believe initial investement value should be added to the earliest cumulative value.

[DaurenA]

@Dangar332 ,

Just noticed that there should be some "IF" condition for var C (remainig useful life in moths):

1. (80-27) = 53 (for those animals whose age starts from 26 or less months)

2. (80 - X), where X is initial age for those animals who were older than 27 months during the initial balance load. X should be greater than 26