Forum Discussion
Calculation within a table
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
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 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
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]
)
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