Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
DaurenA
Helper I
Helper I

Stop calculating cumulative due to change of condition

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.

 

DaurenA_0-1704369797978.png

 

2 ACCEPTED SOLUTIONS

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

 

View solution in original post

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.

 

View solution in original post

13 REPLIES 13
Dangar332
Super User
Super User

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

DaurenA_0-1704391451629.png

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

 

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)

DaurenA_0-1704632858833.png

 

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?

DaurenA_0-1704465892011.png

 

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.

 

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.
 
DaurenA_0-1704641665218.png

 

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?

Then impairment value will be autoadjusted considering initial investment.
 

 

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.

 

@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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.