DAX previous value calculate row value

chaitanya2487 — Mon, 30 Oct 2023 04:43:09 GMT

I'm in need of your assistance with a DAX function.

I'm facing challenges when it comes to calculating a column value based on the previous row's value.

Here's a sample dataset I'm working with:

week number	open count	close count	backlog
30	30	32	40
31	25	15
32	55	60
33	45	40
34	32	28
35	20	26
36	26	24

In the dataset, I want to calculate the "backlog" column, which should be determined by the formula:

backlog = previous week's backlog + opencount - closecount.

For example, for week 31, the backlog should be calculated as the backlog of week 30 + opencount of week 31 - closecount of week 31.

I've attempted to create several DAX expressions, but I keep encountering circular dependency issues. Your assistance in resolving this matter would be greatly appreciated.

Thanks
Chaitanya D

Re: DAX previous value calculate row value

Jihwan_Kim — Mon, 30 Oct 2023 05:19:14 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Backlog measure: = VAR _startweeknumber = CALCULATE ( MIN ( Data[week number] ), REMOVEFILTERS () ) VAR _currentrowweeknumber = MAX ( Data[week number] ) VAR _startbacklog = 40 RETURN IF ( HASONEVALUE ( Data[week number] ), SWITCH ( TRUE (), MAX ( Data[week number] ) = _startweeknumber, _startbacklog, CALCULATE ( SUMX ( Data, Data[open count] - Data[close count] ), FILTER ( ALL ( Data ), Data[week number] > _startweeknumber && Data[week number] <= _currentrowweeknumber ) ) + _startbacklog ) )

topic DAX previous value calculate row value in DAX Commands and Tips

DAX previous value calculate row value

Re: DAX previous value calculate row value