Cumulative calcualtion on the same column

[sonm10]

Hello All,

I have a table with various item and their value for dates. I have calculated the change of their value as return. Now I need to create a column that refers to its previous value of the same column. My data is as follows:

Date	Type	Value	Return	Column
1/1/2020	A	1		100
1/2/2020	A	2	1	200
1/3/2020	A	3	0.5	101
1/4/2020	A	4	0.333333	134.6667
1/5/2020	A	5	0.25	102
1/1/2020	B	7		100
1/2/2020	B	8	0.142857	103
1/3/2020	B	9	0.125	115.875
1/4/2020	B	10	0.111111	104
1/5/2020	B	11	0.1	114.4

I need to calculate the new column whose formula is:

if(return = 0), then 100

else previous value * (1+ return)

It is quite staright forward in excel. We can directly do, E4 = E3*(1+D4)

When i create a similar formula in DAX, i am getting a circular loop error. Any helps is appreciated.

[Icey · Accepted solution]

Hi @sonm10 ,

Please check if this is what you want:

Column 2 =
VAR CurrentDate = [Date]
VAR t =
    ADDCOLUMNS ( 'Table', "Return_1", [Return] + 1 )
RETURN
    PRODUCTX (
        FILTER ( t, [Type] = EARLIER ( [Type] ) && [Date] <= CurrentDate ),
        [Return_1]
    ) * 100

But I have some confusion. Is the data marked above written wrong?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sonm10]

Thanks a lot @Icey . Exactly what i wanted. I had made some mistakes in the sample data and yet you provided exactly what i wanted.

[amitchandak]

@sonm10 , try a new column

if(coalesce([Return],0) =0,100 , [value] * (1+ [return]))

if(([Return]+0) =0,100 , [value] * (1+ [return]))