Calculate with previous row values row by row

[daan1887]

Hi everybody,

we are currently struggling with a row by row calculation with relative values.

The data basis is the following:

Product	Date	DevelopmentToPreviousDate
1	01.01.2018	0%
1	02.01.2018	50%
1	03.01.2018	10%
1	04.01.2018	-50%
1	05.01.2018	25%
1	06.01.2018	10%
1	07.01.2018	100%
2	02.01.2018	0%
2	04.01.2018	10%
2	05.01.2018	25%

We want to calculate the Development of a product but we only have Percentage values that tell us how the product developed from one day to another.

The idea is to assign a value like 100 as a starting point to each product (whenever the product showed up in our data the first time) and then calculate from this value on ongoing row by row to monitoring the development since day one.

Example:

Product	Date	DevelopmentToPreviousDate	Value
1	01.01.2018	0%	100
1	02.01.2018	50%	150
1	03.01.2018	10%	165
1	04.01.2018	-50%	82,5
1	05.01.2018	25%	103,125
1	06.01.2018	10%	113,4375
1	07.01.2018	100%	226,875
2	02.01.2018	0%	100
2	04.01.2018	10%	110
2	05.01.2018	25%	137,5

Do you guys have an idea how to solve this in PowerBI?

Thank you 

Daniel

[v-chuncz-msft · Accepted solution]

@daan1887,

You may use PRODUCTX Function to add a calculated column.

Column =
PRODUCTX (
    FILTER (
        Table1,
        Table1[Product] = EARLIER ( Table1[Product] )
            && Table1[Date] <= EARLIER ( Table1[Date] )
    ),
    Table1[DevelopmentToPreviousDate] + 1
)
    * 100