DAX for generating output from previous output value within calculated column.

[Anonymous]

The data columns marked in green is the source data and one marked in amber is calculated column in excel. This step needs to be automated in power bi (maybe in power query itself or adding new calculated columns in power BI).

• I have tried the output in excel based on data as shown with the applied 'nested if function'. 
• The same needs to be replicated in power bi once the data sheet is imported for source data shown in green and calculated column shown in red highlight.

I tried creating the calculated column in power Bi (named output value) but the real challange is using 
if function in power bi to refer the previous content of the same column. In excel it works pretty fine since we are able to retain and refer the previous value/cells within the same column.

Any suggestion or DAX to build new column ?

[Anonymous]

Hi @Anonymous ,

The green columns already exist in the data source? If so, you could refer the following formula to get the previous index value.

pre_name = 
var p_index = calculate(max([index]),filter('table',[index]<eariler([index])))
return
calculate(max([name]),filter('table',[index] = [p_index]))

pre_end = 
var p_index = calculate(max([index]),filter('table',[index]<eariler([index])))
return
calculate(max([end]),filter('table',[index] = [p_index]))

Then use then in if() function, B13 = [name], B12 = [pre_name], E13 = [end] and E12 = [pre_end].

The thing is calculated column only obtains the value when it is generated, and cannot obtain a value of its own.

In other words, you cannot get the value of H12.

Best Regards,

Jay

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hey @amitchandak please refer to the link containing the sample dataset.

Find here Data

Cheers.