Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Compare previous row with IF function in Power Query Editor

Hi all,    Power BI is new for me and I'm having some struggles with creating the columns I want. Unfortunately, I have not been able to solve this through the available information in the communit...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.