Acumulate field

Question

Hi all,I need to find the accumulated value of an operation. I have this table:COUNTNAMEDATEHDBALANCE52000028BANK131/12/2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19.000,00-&nbsp;&nbsp;&nbsp; 19.000,00&nbsp;&nbsp;01/01/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.640,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp; 17.360,00&nbsp;&nbsp;01/02/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.635,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp; 15.725,00&nbsp;&nbsp;01/03/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.630,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp; 14.095,00&nbsp;&nbsp;01/04/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.625,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp; 12.470,00&nbsp;&nbsp;01/05/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.620,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp; 10.850,00&nbsp;&nbsp;01/06/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.615,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.235,00&nbsp;&nbsp;01/07/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.610,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.625,00&nbsp;&nbsp;01/08/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.605,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.020,00&nbsp;&nbsp;01/09/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.600,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.420,00&nbsp;&nbsp;01/10/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.595,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.825,00&nbsp;&nbsp;01/11/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.590,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.235,00&nbsp;&nbsp;01/12/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21.120,00-&nbsp;&nbsp;&nbsp; 22.355,00&nbsp;&nbsp;TOTAL&nbsp;&nbsp;&nbsp;&nbsp; 17.765,00&nbsp;&nbsp;&nbsp;&nbsp; 40.120,00-&nbsp;&nbsp;&nbsp; 22.355,00&nbsp;and I want to find the column BALANCE that is equal to = Previous balance -D + H with the condition that if it is the first row of the group (COUNT) then it is equal to = -D + H. What is the DAX formula to get it?Thank you.&nbsp;

v-lili6-msft · Answer

hi,&nbsp;Raul&nbsp;
You could use&nbsp;EARLIER Function in this formula to add a new column'
Column = 
VAR _firstdate =
    CALCULATE (
        MIN ( 'Table'[DATE] ),
        FILTER (
            'Table',
            'Table'[COUNT] = EARLIER ( 'Table'[COUNT] )
                &amp;&amp; 'Table'[NAME] = EARLIER ( 'Table'[NAME] )
        )
    )
RETURN
    IF (
        _firstdate = 'Table'[DATE],
        - 'Table'[D] + 'Table'[H],
        - CALCULATE (
            SUM ( 'Table'[D] ),
            FILTER (
                'Table',
                'Table'[DATE] = _firstdate
                    &amp;&amp; 'Table'[COUNT] = EARLIER ( 'Table'[COUNT] )
                    &amp;&amp; 'Table'[NAME] = EARLIER ( 'Table'[NAME] )
            )
        )
            + CALCULATE (
                SUM ( 'Table'[H] ),
                FILTER (
                    'Table',
                    'Table'[COUNT] = EARLIER ( 'Table'[COUNT] )
                        &amp;&amp; 'Table'[NAME] = EARLIER ( 'Table'[NAME] )
                        &amp;&amp; 'Table'[DATE] &lt;= EARLIER ( 'Table'[DATE] )
                )
            ) - 'Table'[D]
    )
Result:

By the way: On 1/110/2019, it should be -4420-0+1695=-2725 not -2825
&nbsp;
here is pbix file, please try it.
&nbsp;
Best Regards,
Lin

raul · Answer

Thank you very much for your help v-lili6-msft,I tried your new calculated column and I'll tell you if it worked for me.Talk to you later.

v-lili6-msft · Answer

hi,&nbsp;Raul&nbsp;
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
&nbsp;
Best Regards,
Lin
&nbsp;

Forum Discussion

Acumulate field

3 Replies

Related content

Acumulative Percentaje

Acumulate fortnight

Field Parameter Question

Calculate an acumulate field until today's date

Owner/Contact Field on reports

COUNT	NAME	DATE	H	D	BALANCE
52000028	BANK1	31/12/2018		19.000,00	- 19.000,00
		01/01/2019	1.640,00	-	- 17.360,00
		01/02/2019	1.635,00	-	- 15.725,00
		01/03/2019	1.630,00	-	- 14.095,00
		01/04/2019	1.625,00	-	- 12.470,00
		01/05/2019	1.620,00	-	- 10.850,00
		01/06/2019	1.615,00	-	- 9.235,00
		01/07/2019	1.610,00	-	- 7.625,00
		01/08/2019	1.605,00	-	- 6.020,00
		01/09/2019	1.600,00	-	- 4.420,00
		01/10/2019	1.595,00	-	- 2.825,00
		01/11/2019	1.590,00	-	- 1.235,00
		01/12/2019		21.120,00	- 22.355,00
		TOTAL	17.765,00	40.120,00	- 22.355,00