Acumulate field

[Raul]

Hi all,
I need to find the accumulated value of an operation. I have this table:

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

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.

[v-lili6-msft]

hi, @Raul 

You could use EARLIER Function in this formula to add a new column'

Column = 
VAR _firstdate =
    CALCULATE (
        MIN ( 'Table'[DATE] ),
        FILTER (
            'Table',
            'Table'[COUNT] = EARLIER ( 'Table'[COUNT] )
                && 'Table'[NAME] = EARLIER ( 'Table'[NAME] )
        )
    )
RETURN
    IF (
        _firstdate = 'Table'[DATE],
        - 'Table'[D] + 'Table'[H],
        - CALCULATE (
            SUM ( 'Table'[D] ),
            FILTER (
                'Table',
                'Table'[DATE] = _firstdate
                    && 'Table'[COUNT] = EARLIER ( 'Table'[COUNT] )
                    && 'Table'[NAME] = EARLIER ( 'Table'[NAME] )
            )
        )
            + CALCULATE (
                SUM ( 'Table'[H] ),
                FILTER (
                    'Table',
                    'Table'[COUNT] = EARLIER ( 'Table'[COUNT] )
                        && 'Table'[NAME] = EARLIER ( 'Table'[NAME] )
                        && 'Table'[DATE] <= EARLIER ( 'Table'[DATE] )
                )
            ) - 'Table'[D]
    )

Result:

By the way: On 1/110/2019, it should be -4420-0+1695=-2725 not -2825

here is pbix file, please try it.

Best Regards,

Lin

[Raul]

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]

hi, @Raul 

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Best Regards,

Lin