Forum Discussion

sonm10's avatar
sonm10
Icon for Helper I rankHelper I
6 years ago
Solved

Cumulative calcualtion on the same column

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:

 

DateTypeValueReturnColumn
1/1/2020A1 100
1/2/2020A21200
1/3/2020A30.5101
1/4/2020A40.333333134.6667
1/5/2020A50.25102
1/1/2020B7 100
1/2/2020B80.142857103
1/3/2020B90.125115.875
1/4/2020B100.111111104
1/5/2020B110.1114.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.

 

Need Help
Show and Tell
Tips and Tricks
  • Icey's avatar
    Icey
    6 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    sonm10 , try a new column

     

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

     

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

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      sonm10
      Icon for Helper I rankHelper I
      6 years ago

      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.