Compare previous row with IF function in Power Query Editor

[Anonymous]

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 community and online. So I hope you can help me with this. 

Simply said, I want to compare a row with the previous row and based on this results, I want to make a calculation for it. 

In Excel I used the following formula for it:

The formula is: =IF(AND(B3=B2;L3=L2;NOT(O3=O2));IF(R3=R2;D3-D2;52-D2+D3);"NOT")

It compares if the service provider is the same as the one in the row above, then if the zip codes are the same and then if the service is not the same. If this is true, the formula will look if the years are the same. If this is the case, the difference in week numbers will be calculated, to calculate the lead time in weeks. If the years are not the same, the calculation will be: 52-weeknbr D2 + weeknbr D3. When the service provider or zipcode is not the same, or if the row above is not another service, the value will be "NOT". 

I want to make this calculation also in Power BI, in the Power Query Editor. But it doesn't work. 

I tried to do this in two times (because that seems easier to me); first look if the installation and after sale is from the same customer (same service provider, same zip code and both services) and then look at the calculation for the lead time in weeks. 

I used custom column with this formula (for the first part):

= if (AND([Service Provider]=LOOKUPVALUE([Service Provider],[Index],[Index]-1)
,[Zipcode]=LOOKUPVALUE([Zipcode],[Index],[index]-1)
,(NOT([Service]=LOOKUPVALUE([Service],[Index],[Index]-1))))
,if ([Year]=LOOKUPVALUE([Year],[Index],[Index]-1)
,[Week]-LOOKUPVALUE([Week],[Index],[Index]-1)
,52-[Week]+LOOKUPVALUE([Week],[Index],[Index]-1)
,"NOT"))

But this gives an error as Right Paren expected. It seems that the M and DAX codes are mixed in the formula.

I hope the problem and explanation is understandable.

Thank you in advance!  

[v-alq-msft · Accepted solution]

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.

[romovaro]

HI Allan,

Hope you are doing fine. I have an issue similar to the one you solved.

I need help with one formula I have in Excel.

=IF(A2<>A3,1,IF(AND(A2=A3,D2<>D3),1,IF(AND(A2=A3,D2=D3,E2=E3),0,1)))

Where A is CID Number

Where D is Country

Where E is GLD

The idea is to count the number of projects. If a Customer (CID) have CUIDs with the same country and Same GLD (Go live Date)

From the table below, the ones highlighted in red are considered 1 project.

CID	Client Name	Id Country	CUID	Country	GLD	Num Projects
10002	Customer A	US	CustA1	United States	9/1/2022	0
10002	Customer A	US	CustA2	United States	9/1/2022	1
10022	Customer B	IN	CustB1	India	12/1/2021	1
10022	Customer B	SE	CustB2	Sweden	4/1/2024	1
10027	Customer C	AU	CustC1	Australia	5/1/2022	1
10038	Customer D	EG	CustD1	Egypt	3/1/2024	1
10047	Customer E	SG	CustE1	Singapore	7/1/2022	1
10065	Customer F	SG	CustF1	Singapore	6/1/2022	1
10109	Customer G	AT	CustG1	Austria	8/1/2022	1
10109	Customer G	BR	CustG2	Brazil	6/1/2023	0
10109	Customer G	BR	CustG3	Brazil	6/1/2023	0
10109	Customer G	BR	CustG4	Brazil	6/1/2023	1
10109	Customer G	CR	CustG5	Costa Rica	7/1/2022	1
10109	Customer G	DK	CustG6	Denmark	5/1/2023	1
10109	Customer G	EE	CustG7	Estonia	11/1/2021	1
10109	Customer G	EE	CustG8	Estonia	2/1/2024	1
10109	Customer G	DE	CustG9	Germany	10/1/2021	1
10109	Customer G	GR	CustG10	Greece	10/1/2021	1
10109	Customer G	KR	CustG11	Korea, Republic Of	2/1/2022	1
10109	Customer G	LV	CustG12	Latvia	10/1/2021	0
10109	Customer G	LV	CustG13	Latvia	10/1/2021	1
10109	Customer G	LT	CustG14	Lithuania	10/1/2021	1
10109	Customer G	LU	CustG15	Luxembourg	1/1/2024	1
10109	Customer G	MX	CustG16	Mexico	2/1/2022	0
10109	Customer G	MX	CustG17	Mexico	2/1/2022	1
10109	Customer G	NO	CustG18	Norway	2/1/2024	1
10109	Customer G	PA	CustG19	Panama	7/1/2022	1
10109	Customer G	RS	CustG20	Serbia	2/1/2024	1
10109	Customer G	SG	CustG21	Singapore	2/1/2022	1

for the Excel formula to work, I have the Excel sorted to get CID/Country and GLD (from Oldest to Newest). and the formula filters if Same CID, Same COuntry (from previous row) And Same GLD (from previous Row)

= 0 and if not = 1. Then I can count the number of projects.

1 - I created a Custom Sort in Power Query: CID (Ascending), Country (ascending) and GLD (Ascending)

2 - Created an Index Column

I was proposed to use the formula below but is not working for me. 

Num Projects =

VAR CurrentRow = 'Weekly Slippage'

VAR PreviousRow =

CALCULATETABLE(

'Weekly Slippage',

'Weekly Slippage'[Index] = CurrentRow[Index] - 1

)

RETURN

IF (

CurrentRow[CID] <> PreviousRow[CID],

1,

IF (

AND(

CurrentRow[CID] = PreviousRow[CID],

CurrentRow[Country] <> PreviousRow[Country]

),

1,

IF (

AND(

CurrentRow[CID] = PreviousRow[CID],

CurrentRow[Country] = PreviousRow[Country],

CurrentRow[GLD] = PreviousRow[GLD]

),

0,

1

)

)

)

The variable is not being recognized.

I also have the error: Too many arguments have been passed to the AND function. The maximum number of arguments for the function is 2.

Thanks,

[Anonymous]

Thanks for your help @v-alq-msft, 

It works! 🙂

[Greg_Deckler]

@Anonymous - You are using DAX code in Power Query Editor, that will not work. If you want a DAX solution, you would use EARLIER like in this article. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

For Power Query there are techiques for this that I have seen from folks like @ImkeF 

[amitchandak]

@Anonymous , refer these

https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/

https://www.youtube.com/watch?v=90EYX7pzVlE