Compare previous row with IF function in Power Query Editor

Hi, Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may go to 'Query Editor', click 'Add Column'=> 'Index Column'.

Then you may create a calculated column or a measure as below.

Calculated column:
Column = 
var _store = [Store]
var _index = [Index]
var _serviceprovider = [Service Provider]
var _week = [Week]
var _zipcode = [Zipcode]
var _service = [Service]
var _year = [Year]
var _laststore = 
LOOKUPVALUE('Table'[Store],'Table'[Index],_index-1)
var _lastserviceprovider = 
LOOKUPVALUE('Table'[Service Provider],'Table'[Index],_index-1)
var _lastweek = 
LOOKUPVALUE('Table'[Week],'Table'[Index],_index-1)
var _lastzipcode = 
LOOKUPVALUE('Table'[Zipcode],'Table'[Index],_index-1)
var _lastservice = 
LOOKUPVALUE('Table'[Service],'Table'[Index],_index-1)
var _lastyear = 
LOOKUPVALUE('Table'[Year],'Table'[Index],_index-1)
return
IF(
    _index-1<0,
    "NOT",
    IF(
        _laststore=_store,
        IF(
            _lastserviceprovider=_serviceprovider&&
            _lastzipcode=_zipcode&&
            _lastservice<>_service,
            IF(
                _lastyear=_year,
                (_week-_lastweek)&"",
                (52-_lastweek+_week)&""
            ),
            "NOT"
        ),
        "NOT"
    )
)

Measure:
Measure = 
var _store = SELECTEDVALUE('Table'[Store])
var _index = SELECTEDVALUE('Table'[Index])
var _serviceprovider = SELECTEDVALUE('Table'[Service Provider])
var _week = SELECTEDVALUE('Table'[Week])
var _zipcode = SELECTEDVALUE('Table'[Zipcode])
var _service = SELECTEDVALUE('Table'[Service])
var _year = SELECTEDVALUE('Table'[Year])
var _laststore = 
LOOKUPVALUE('Table'[Store],'Table'[Index],_index-1)
var _lastserviceprovider = 
LOOKUPVALUE('Table'[Service Provider],'Table'[Index],_index-1)
var _lastweek = 
LOOKUPVALUE('Table'[Week],'Table'[Index],_index-1)
var _lastzipcode = 
LOOKUPVALUE('Table'[Zipcode],'Table'[Index],_index-1)
var _lastservice = 
LOOKUPVALUE('Table'[Service],'Table'[Index],_index-1)
var _lastyear = 
LOOKUPVALUE('Table'[Year],'Table'[Index],_index-1)
return
IF(
    _index-1<0,
    "NOT",
    IF(
        _laststore=_store,
        IF(
            _lastserviceprovider=_serviceprovider&&
            _lastzipcode=_zipcode&&
            _lastservice<>_service,
            IF(
                _lastyear=_year,
                (_week-_lastweek)&"",
                (52-_lastweek+_week)&""
            ),
            "NOT"
        ),
        "NOT"
    )
)

Result:

Best Regards

Allan

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