Calculation within a table

[JuliaWendel]

Hello,

I'm not very experienced with PowerBI, maybe anybody can help me out with my issue:

I would like to add a ne column and the cells should be calculated for each row new:

I have a start value e.g. = 200 --> active products

Two columns for the calculation are given by the data base: 1) "product launch" and the other is 2)  "product expire"

Example:

Start value: 200

Date: January

Product launch: +2

Product expire: -1

--> Calculate active products for January and write it in new column: 200 + 2 - 1 = 201 active products

Start Value 201

Date: February

Product launch: +3

Product expire: -1

--> Calculate active products for February and write it in the cell below the upper calculation: 201+3-1: 203 active products

Is it possible with the SUMX formula?

Thank you in advance!!!

Julia

[Eric_Zhang · Accepted solution]

---

@JuliaWendel wrote:

Hello,

I'm not very experienced with PowerBI, maybe anybody can help me out with my issue:

I would like to add a ne column and the cells should be calculated for each row new:

I have a start value e.g. = 200 --> active products

Two columns for the calculation are given by the data base: 1) "product launch" and the other is 2)  "product expire"

Example:

Start value: 200

Date: January

Product launch: +2

Product expire: -1

--> Calculate active products for January and write it in new column: 200 + 2 - 1 = 201 active products

Start Value 201

Date: February

Product launch: +3

Product expire: -1

--> Calculate active products for February and write it in the cell below the upper calculation: 201+3-1: 203 active products

Is it possible with the SUMX formula?

Thank you in advance!!!

Julia

---

@JuliaWendel

So my understanding is, it is actually 200 + accumulative(product launch - product expire) in your case. Then you can create a measure as 

active products = 
200
    + SUMX (
        FILTER ( ALL ( yourtable ), yourtable[date] <= MAX ( yourtable[date] ) ),
        yourtable[product launch] - yourtable[product expire]
    ) 

[JuliaWendel]

@Eric_Zhang Thank you very much, it worked out very well!

I got another question:

is it possible to calculate the difference within one column between two months?

E.g. 

Month: January

Product launch: 5

Month: February

Product launch: 7

--> calculate in new column the difference between "Product launch" February and "Product launch" January:

7-5 = +2

Explanation

Maybe you now a solution or anybody else.

Thank you in advance!

Julia