DAX previous value calculate row value

[chaitanya2487]

Hi 

 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

[Jihwan_Kim · Accepted solution]

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
        )
    )