amount by deducting the sum from previous month's result amount and that resulting amount should

[Anonymous]

Hi Team

I need your help to write a dax function to calculate the amount from previous resulting amount and then that result should serve as a basis for next calculating amount and so on and hence forth.

Below is the dummy data for the same

Month Index	Record amont	Month
0	248500443	May-19
1	238252367	Apr-19
2	238251056	Mar-19
3	238251056	Feb-19
4	-8469733	Jan-19
5	-8515208	Dec-18
6	-8516008	Nov-18
7	-8518568	Oct-18
8	-8519368	Sep-18

	Month Index	LAMT Amount	Remaining balance
May-19	0	248,075.54	248,252,367.18	(Record Amount -LAMT Amount where month Index 0)
Apr-19	1	1,311.56	248,251,055.62	(May-19 remaining Balance )-(Apr -19 LAMT amount)
Mar-19	2	-	248,251,055.62	(Apr-19 remaining Balance )-(Mar -19 LAMT amount)
Feb-19	3	246,720,788.38	1,530,267.24	So on and So forth
Jan-19	4	45,475.00	1,484,792.24
Dec-18	5	800.00	1,483,992.24
Nov-18	6	2,560.00	1,481,432.24
Oct-18	7	950.00	1,480,482.24
Sep-18	8	2,665.00	1,477,817.24
Aug-18	9	-	1,477,817.24
Jul-18	10	-	1,477,817.24
Jun-18	11	25,960.00	1,451,857.24
May-18	12	3,105.71	1,448,751.53
Apr-18	13	182,400.50	1,266,351.03
Mar-18	14	-	1,266,351.03
Feb-18	15	4,550.00	1,261,801.03
Jan-18	16	-	1,261,801.03

Thanks, 

KK

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could use this formula to create a measure

Measure = 
CALCULATE (
    SUM ( Dim[Record amont] ),
    FILTER (
        Dim,
        Dim[Month Index] = CALCULATE ( MIN ( Table1[Index] ), ALL ( Table1 ) )
    )
)
    - CALCULATE (
        SUM ( Table1[LAMT Amount] ),
        FILTER ( ALL ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) )
    )

Result:

here is pbix file, please try it.

Best Regards,

Lin