cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Stop calculating cumulative due to change of condition

Hi Friends,

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

2 ACCEPTED SOLUTIONS
Super User

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]
)``````

Super User

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

13 REPLIES 13
Super User

hi, @DaurenA

try below code for column

``````column =
SUMX(
FILTER(
'movement',
'movement'[age]<=EARLIER('movement'[age]) &&
not('movement'[age]>26)
),
)``````
Helper I

Hi @Dangar332 ,

Unfortunately it didn't work

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

CALCULATE(
SUM( Movement[monthly cost] ),
FILTER(
ALL( Movement ),
Movement[Unique Ident #]
= EARLIER( Movement[Unique Ident #] )
&&Movement[Forecast Day]
<=EARLIER( Movement[Forecast Day] )

)
)

Super User

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]
)``````

Helper I

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)

Helper I

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.

Super User

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)?

Helper I

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

Super User

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

Helper I

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.

Super User

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)``````

Super User

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.

Helper I

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.

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.