Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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]
)
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.
hi, @DaurenA
try below code for column
column =
SUMX(
FILTER(
'movement',
'movement'[age]<=EARLIER('movement'[age]) &&
not('movement'[age]>26)
),
'movement'[cumulative add col]
)
Hi @Dangar332 ,
Unfortunately it didn't work
This my code. May be needto modify it, so it stops cumulating after 26 age.
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]
)
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)
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.
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)?
Hi @Dangar332
Please see below screen with comments.
How can I share with the model itself? Can it be attached here?
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.
Hi @Dangar332 ,
Thank you very much! By the way how kudos are assigned?
That worked, however I did some little changes like below.
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)
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?
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |