Forum Discussion

daan1887's avatar
daan1887
New Member
8 years ago
Solved

Calculate with previous row values row by row

Hi everybody,

 

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

 

The data basis is the following:

ProductDateDevelopmentToPreviousDate
101.01.20180%
102.01.201850%
103.01.201810%
104.01.2018-50%
105.01.201825%
106.01.201810%
107.01.2018100%
202.01.20180%
204.01.201810%
205.01.201825%

 

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:

ProductDateDevelopmentToPreviousDateValue
101.01.20180%100
102.01.201850%150
103.01.201810%165
104.01.2018-50%82,5
105.01.201825%103,125
106.01.201810%113,4375
107.01.2018100%226,875
202.01.20180%100
204.01.201810%110
205.01.201825%137,5

 

 

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

 

 

Thank you :manhappy:

Daniel

  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    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

1 Reply

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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