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

[sensemaker14]

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 separate out contributed change without the initial $1,000 included, then I need to combine Income + Contributed Change values.

Year	Quarter	Month	Income	Contributed Capital	Contributed Change	Combined
2021	Q1	Mar	$200.00	$1,000.00	$0.00	$200.00
2021	Q2	Jun	$400.00	$1,100.00	$100.00	$500.00
2021	Q3	Sep	$100.00	$1,100.00	$0.00	$100.00
2021	Q4	Dec	$200.00	$1,300.00	$200.00	$400.00
2022	Q1	Mar	$200.00	$1,300.00	$0.00	$200.00
2022	Q2	Jun	$300.00	$1,800.00	$500.00	$800.00

[Ashish_Mathur]

Do you also have Year and Month (as shown in the image of your post)) in the dataset?

[sensemaker14]

The date in my financial table is related to the date in my calendar table.

Let me know if that doesn't answer your question well enough.

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.  Do you want a measure or a calculated column?

[v-binbinyu-msft]

Hi @sensemaker14,

Please try below steps:

1. add a new index column to the table

2. create two measure with below dax formula

Adjust Contribute =
VAR cur_index =
    SELECTEDVALUE ( 'Table'[Index] )
VAR cur__ct =
    CALCULATE (
        MAX ( 'Table'[Contributed Capital] ),
        'Table'[Index] = cur_index,
        ALL ( 'Table' )
    )
VAR pre_ct =
    CALCULATE (
        MAX ( 'Table'[Contributed Capital] ),
        'Table'[Index] = cur_index - 1,
        ALL ( 'Table' )
    )
RETURN
    SWITCH ( TRUE (), cur_index = 1, 0, cur_index = 2, cur__ct, cur__ct - pre_ct )

Income add Contribute =
VAR cur_income =
    SELECTEDVALUE ( 'Table'[Income] )
VAR cur_ac = [Adjust Contribute]
VAR _val = cur_income + cur_ac
RETURN
    IF ( _val > 0, "+" & _val, "-" & _val )

3. add a table visual with fields and measure

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]

Thank you for your response.

I am trying to achieve the values listed in the "Combined" column, while ignoring the inital contributed capital value.

[v-binbinyu-msft]

Hi @sensemaker14 ,

As my above screenshot show, please refer "Income add Contribute" measure, the logic of his calculation is consistent with what you said.

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]

Hi @v-binbinyu-msft 
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

[v-binbinyu-msft · Accepted solution]

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.