Forum Discussion

sensemaker14's avatar
sensemaker14
Frequent Visitor
3 years ago
Solved

How do I pull out amt changed from cumulating sum while ignoring initial value?

Greetings community, Please let me know if you have any ideas for me! I'm struggling with how to ignore an initial value. I have Income and Contributed Capital provided in the data.  I need to...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi sensemaker14 ,

    I adjust something, please try below steps:

    1. add a new column with below dax formula

    RK =
VAR cur_year = [Year]
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] = cur_year )
RETURN
    RANKX ( tmp, [Index],, ASC, DENSE )

    2. use this dax formula to replace above same name measure

    Adjust Contribute =
VAR cur_rk =
    SELECTEDVALUE ( 'Table'[RK] )
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR cur__ct =
    CALCULATE (
        MAX ( 'Table'[Contributed Capital] ),
        'Table'[RK] = cur_rk
            && 'Table'[Year] = cur_year,
        ALL ( 'Table' )
    )
VAR pre_ct =
    CALCULATE (
        MAX ( 'Table'[Contributed Capital] ),
        'Table'[RK] = cur_rk - 1
            && 'Table'[Year] = cur_year,
        ALL ( 'Table' )
    )
RETURN
    IF ( cur_rk = 1, 0, cur__ct - pre_ct )

    3. then you could get the output

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

sensemaker14's avatar
sensemaker14
Frequent Visitor
3 years ago

Hi Anonymous 
I don't know how to attach the .pbix like you did, it errors and says the file type isn't allowed. 

 

But the only thing I'm missing is the initial ignore.

 

Adjusted Contribute =
var current_index=SELECTEDVALUE('Table'[Index])
var current__capital=CALCULATE(MAX('Table'[Contributed Capital]),'Table'[Index]=current_index,ALL('Table'))
var previous_capital=CALCULATE(MAX('Table'[Contributed Capital]),'Table'[Index]=current_index-1,ALL('Table'))
return
current__capital-previous_capital

 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi sensemaker14 ,

I adjust something, please try below steps:

1. add a new column with below dax formula

RK =
VAR cur_year = [Year]
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] = cur_year )
RETURN
    RANKX ( tmp, [Index],, ASC, DENSE )

2. use this dax formula to replace above same name measure

Adjust Contribute =
VAR cur_rk =
    SELECTEDVALUE ( 'Table'[RK] )
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR cur__ct =
    CALCULATE (
        MAX ( 'Table'[Contributed Capital] ),
        'Table'[RK] = cur_rk
            && 'Table'[Year] = cur_year,
        ALL ( 'Table' )
    )
VAR pre_ct =
    CALCULATE (
        MAX ( 'Table'[Contributed Capital] ),
        'Table'[RK] = cur_rk - 1
            && 'Table'[Year] = cur_year,
        ALL ( 'Table' )
    )
RETURN
    IF ( cur_rk = 1, 0, cur__ct - pre_ct )

3. then you could get the output

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.