How can I use values in the same column for a calculation?

[Anonymous]

Here's some sample data to illustrate my problem:

	A	B	C	D
1		Value	Pct%Ret	Index
2	01-Aug	750,000		100
3	02-Aug	759,000	1.01	101.2
4	03-Aug	780,000	1.03	104
5	04-Aug	740,000	0.95	98.66667

The Pct%Ret column is calculated by dividing the most recent value by the previous value (so for C3 was calculated by B3/B2). Since there is no previous value on Aug 01, then Pct%Ret is blank. 

For the Index column, the first value is always 100 (so this is a fixed value). The subsequent values are calculated by getting the previous day's index value and multiplying it by the current day's Pct%Ret. So for D3 = D2 * C3 and D4 = D3 * C4.

I have already created the Pct%Ret column, but i'm unsure how to use the values in the same column as it is returning an error. How can I complete this in PowerBI?

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to productx the [Pct %] measure and your default value is 100.

Here are the steps you can refer to :
(1)This is my test data :

I create the [Pct %] measure to simulate your situation:

(2)Then we can just create a measure like this:

Index = var _default = 100 
 var _t = ADDCOLUMNS( ALLSELECTED('Table'[Date]) , "Pct" ,  IF( [Pct%]=BLANK() ,1, [Pct%]))
 var _t2 = FILTER(_t , [Date]<= MAX('Table'[Date]))
 return
PRODUCTX(_t2 , [Pct])*_default

Then we can put this measure on the visual and we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

Do you want a "measure" or a "calculated column" formula solution?

[Anonymous]

I would like a calculated column solution, please. I plan to create an Indexed chart with base 100

[Ashish_Mathur]

Hi,

This calculated column formula works

Growth (%) = DIVIDE(Data[Value],CALCULATE(MAX(Data[Value]),FILTER(Data,Data[Date]=MIN(Data[Date]))))

Hope this helps.

[amitchandak]

@Anonymous , You can use ealier

Example

New column=

var _max = maxx(filter(Table, [A] < earlier([A]) ), [A])

return

divide([B],  sumx(filter(Table, [A] = _max), [B]) )

Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[Anonymous]

I've tried using EARLIER(), here is my code for the Index column.

Index = 
VAR BaseValue = 100
VAR Multiplier = NLV[Pct%Ret]
VAR CurrentRow = NLV[Row]
RETURN
    IF(
        CurrentRow = 1185,
        BaseValue,
        EARLIER([Index]) * Multiplier
    )

However, the error it gives me is "EARLIER/EARLIEST refers to an earlier row context which doesn't exist.". Any idea how to calculate the Index column using the calculated values in the same column?