Forum Discussion

Bokangmaela's avatar
Bokangmaela
Helper I
3 years ago

Calculated Columns offset value

Good day  I am current try to replicate a totalizer reconstructure into power bi using a calculated column. Lets say we have a column named Tagvalues which hold a list of values.  I have created a...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

Bokangmaela , If you want a new column

 

Previous Tage value = maxx(filter(Table, [Index] = earlier([Index]) -1), [Tag Value])

 

for measure consider Offset

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

Bokangmaela's avatar
Bokangmaela
Helper I
3 years ago

Thanks for taking your time to look at my question.

 

I am able to calculate the previouse the Previouse Tag value using a calculated column. The issue that I am facing is while calculating each row of a column I need to be able to look at the previous row of the column in order to calculate the next value of the column

 

For example 

Lets have a look at row index 3. The row value in the New Value column at Row Index 3 is calculated by using the [New Value column at Row Index 2] +  [Current tag value column at Row Index 3] - [Previous tag value column at Row Index 3].  The issue I am have is getting the [New Value column at Row Index 2] in order to calculate the value for the New value column at Row Index 3.  So you can't calculate the next row value without using the previous row value.

 

If I am using your example I would want to do something like 

Previous Tage value =
var PreviouseValue = maxx(filter(Table, [Index] = earlier([Index]) -1), [Previous Tage value])
 
Here is the Excel equivalent

 

 

Please let me know if my explanation provides more clarity in the issue that I am facing.