DAX to calculate value from previous row in the same column

[cj_oat]

Hi all,

I would like to seek your advices for the case below, so I'm trying the get the result as column highlighted in yellow where the calculation in excel is to show the number under column "Active Period" if "Active Flag" = 1 and "Month" is equal to "Effective Month" but if it's FALSE then if value from previous month is > 0 then minus that value by 1 (regardless of eligibility is 1 or 0)

Appreciated your support, thank you.

[Anonymous · Accepted solution]

Thank you rajendraongole1 

Hi, @cj_oat 

Based on the information shown in your screenshot, I created the following dataset:

First, create an index column in Power Query:

Create column1 settlement column:

Column1 = 
VAR _season = 'Table'[Season]
VAR _season1 = CALCULATE(MAX('Table'[Season]),FILTER('Table','Table'[Season]=EARLIER('Table'[Season])))
VAR _res1 = IF('Table'[Month]='Table'[Effective Month]&&'Table'[Active Flag]=1,'Table'[Active Period (Month)])
RETURN IF(_season=_season1,_res1)

Create column2 calculated column:

Column 2 = 
VAR _index = CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),'Table'[Season]=EARLIER('Table'[Season])&&'Table'[Column1]<>BLANK()))
VAR _index_value = CALCULATE(MAX('Table'[Column1]),FILTER(ALL('Table'),'Table'[Season]=EARLIER('Table'[Season])&&'Table'[Column1]<>BLANK()))
VAR _current_index = CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[Season]=EARLIER('Table'[Season])&&'Table'[Index]<=EARLIER('Table'[Index])))
RETURN IF(_current_index>_index
,_index_value-CALCULATE(COUNTA('Table'[Season]),FILTER('Table','Table'[Season]=EARLIER('Table'[Season])&&'Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>_index)),
IF(_index=_current_index,_index_value,0)

)

Return target result:

CountdownTime = 
VAR _season_index = CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[Season]=EARLIER('Table'[Season])&&'Table'[Column 2]<0))-1
VAR _season_index_value =CALCULATE(MAX('Table'[Column 2]),FILTER('Table','Table'[Index] = _season_index))
VAR _index = 'Table'[Index]
RETURN IF(_index<=_season_index,'Table'[Column 2],IF(_season_index_value+'Table'[Column 2]>0,_season_index_value+'Table'[Column 2],0))

I have uploaded the PBIX file I used for this post below.

Best Regards

Jianpeng Li

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

[cj_oat]

HI @rajendraongole1 ,

Thanks for your advices! However, I still facing with the circular issue

"A circular dependency was detected: Table[CalculatedResult]."

Do you have any advices to solve this error?

[rajendraongole1]

Hi @cj_oat -Instead of a calculated column, you can use a calculated measure that avoids circular dependencies by applying running logic to calculate the desired result.

CalculatedMeasure =
VAR CurrentMonth = 'Table'[Month]
VAR PreviousMonthValue =
CALCULATE(
MAX('Table'[Active Period]),
FILTER(
'Table',
'Table'[Month] = EARLIER('Table'[Month]) - 1
)
)
RETURN
IF(
'Table'[Active Flag] = 1 && 'Table'[Month] = 'Table'[Effective Month],
'Table'[Active Period],
IF(
PreviousMonthValue > 0,
PreviousMonthValue - 1,
0
)
)

Hope it works, please check.

[rajendraongole1]

Hi @cj_oat - you can create a calculated column taking the month, active flag as below

CalculatedResult =
VAR CurrentMonth = [Month]
VAR PreviousValue =
CALCULATE(
MAX('Table'[CalculatedResult]),
FILTER(
'Table',
'Table'[Month] = EARLIER('Table'[Month]) - 1
)
)
RETURN
IF(
'Table'[Active Flag] = 1 && 'Table'[Month] = 'Table'[Effective Month],
'Table'[Active Period],
IF(
PreviousValue > 0,
PreviousValue - 1,
0
)
)

it will apply this logic across all rows, generating the output similar to the Excel behavior you described.

hope it works in your file too.