# DAX Earlier function



## estgas (Jun 26, 2015)

Hi, I am struggling with DAX EARLIER function in order to populate the FX column with a formula than in excel would be straight forward: formula in cell D2 =IF(C2="X";C2;IF(B2=B1;D1;""))

The row in column FX should be populated with column 3 value if existing, otherwise with the the previous row of FX, this by column 2 groups and Date ascending.

Date  2 3 FX
1.Jan A X X
2.Jan AX
3.Jan A    X
1.Jan B 
2.Jan B X X
3.Jan B    X
4.Jan B    X
4.Jan C 
5.Jan C 
6.Jan C X X
7.Jan C    X
8.Jan C    X


Could you please help me?
Many thanks in advance, Est Gas.


----------



## estgas (Jun 26, 2015)

Just noticed that the example I originally posted is not that readable, this might explain better:

Date  2 3 FX
1.Jan A X X
2.Jan A _ X
3.Jan A _ X
1.Jan B 
2.Jan B X X
3.Jan B _ X
4.Jan B _ X
4.Jan C 
5.Jan C 
6.Jan C X X
7.Jan C _ X
8.Jan C _ X


----------



## estgas (Jun 26, 2015)

I found a similar question that solves my question: Can I compare values in the same column in adjacent rows in PowerPivot? - Stack Overflow


----------



## Matt Allington (Jun 27, 2015)

If you want to reference another column in DAX, you need a unique column ID. This can use a lot of space in very large tables. Did the above link solve your problem for you?


----------



## Matt Allington (Jun 27, 2015)

Take a look at this post. How to Compare the Current Row to the Previous Row in Power Pivot - PowerPivotPro PowerPivotPro


----------



## estgas (Jun 27, 2015)

Many thanks for your reply Matt! Before writing to MR Excel I read several posts including the one you mentioned and I was able to create the index column but i am not able to create a unique index.

Please see the example below, my target is to build a calculated TARGET column that get 'X' when in the CTRL column there is an X, also for following records in time and separate for ABC.

I really hope you could help me in doing this in DAX, when in excel it takes 5''  ...


Date ABC CTRL TARGET
1.1.2015 A X X
2.1.2015 A _ X
3.1.2015 A _ X
1.1.2015 B 
2.1.2015 B X X
3.1.2015 B _ X
4.1.2015 B _ X
3.1.2015 C 
4.1.2015 C X X
*6*.1.2015 C _ X
*6*.1.2015 C _ X
7.1.2015 C _ X
8.1.2015 C _ X


----------



## Matt Allington (Jun 27, 2015)

Can you post the exact expected values for the new column. Sorry, I don't understand what you need.


----------



## estgas (Jun 27, 2015)

The expected values are in column TARGET.

The table file do have 4 columns: Date (not unique for ABC, please see 6th of Jan.), ABC, CTRL that in some records has 'X', and the Target calculated column that should get X (also for the following records in time by ABC) when the CTRL column do have X.

I hope the below table is understandable otherwise please let me know how could I post it:


Date ABC CTRL TARGET
1.1.2015 A X X
2.1.2015 A _ X
3.1.2015 A _ X
1.1.2015 B 
2.1.2015 B X X
3.1.2015 B _ X
4.1.2015 B _ X
3.1.2015 C 
4.1.2015 C X X
*6*.1.2015 C _ X
*6*.1.2015 C _ X
7.1.2015 C _ X
8.1.2015 C _ X


----------



## Matt Allington (Jun 27, 2015)

Do you specifically need a calculated column for this?  It is very common for new users of Power Pivot to think they need calc columns, when often a calculated field in a Pivot Table will work just fine

Anyway, this formula seems to work (adds a 1 in the Target column)

```
=CALCULATE (    COUNTA ( Data[CTRL] ),
    FILTER ( Data, Data[ABC] = EARLIER ( Data[ABC] ) ),
    FILTER ( Data, Data[Date] <= EARLIER ( Data[Date] ) )
)
```

https://dl.dropboxusercontent.com/u/30711565/earlier.xlsx


----------



## estgas (Jun 27, 2015)

Many Many Thanks Matt!

This is like 10 steps forward now, and yes sorry I am new user in PowerPivot...

The last thing I am missing is that the CTRL column can have 'X' or 'Y' values, then is  there a way in the Target calculated column to show 'X' or 'Y' instead of 1 ?


----------



## estgas (Jun 26, 2015)

Hi, I am struggling with DAX EARLIER function in order to populate the FX column with a formula than in excel would be straight forward: formula in cell D2 =IF(C2="X";C2;IF(B2=B1;D1;""))

The row in column FX should be populated with column 3 value if existing, otherwise with the the previous row of FX, this by column 2 groups and Date ascending.

Date  2 3 FX
1.Jan A X X
2.Jan AX
3.Jan A    X
1.Jan B 
2.Jan B X X
3.Jan B    X
4.Jan B    X
4.Jan C 
5.Jan C 
6.Jan C X X
7.Jan C    X
8.Jan C    X


Could you please help me?
Many thanks in advance, Est Gas.


----------



## Matt Allington (Jun 28, 2015)

Yes, just wrap my code in an if statement. 


```
=IF(<my code here>=1,"X","Y")
```


----------



## estgas (Jun 28, 2015)

Thank you Matt, sorry i was not clear, I still hope you could help me.

In the CTRL column there are 'X's and 'Y's, how would it be possible to get the same in the Target column? (to calculate the same in excel I use in column D2: =IF(OR(C3="X";C3="Y");C3;IF(B3=B2;D2;"")).


Date ABC CTRL TARGET
1.1.2015 A X X
2.1.2015 A _ X
3.1.2015 A _ X
1.1.2015 B 
2.1.2015 B *Y* *Y*
3.1.2015 B _ *Y*
4.1.2015 B _ *Y*
3.1.2015 C 
4.1.2015 C X X
6.1.2015 C _ X
6.1.2015 C _ X
7.1.2015 C _ X
8.1.2015 C _ X


----------



## estgas (Jun 28, 2015)

Please let me explain why I am trying to build the target column.
My table is a *warehouse movements table *with date, material (ABC..) and the CTRL column that is the supplier that can be either X or Y supplier (clearly in the table there is more like the movement type etc).
Applying LIFO, when a X supplier delivers a certain material (ABC) I assume that following sales are generated from that supplier X.
I am looking forward to build the target column in a calculated column in order to use it in the Pivot Tables as column, row or filter.


----------



## Matt Allington (Jun 28, 2015)

Try this


```
=CALCULATE (    FIRSTNONBLANK( ( Data[CTRL] ),1),
    FILTER ( Data, Data[ABC] = EARLIER ( Data[ABC] ) ),
    FILTER ( Data, Data[Date] <= EARLIER ( Data[Date] ) )
)
```


----------



## estgas (Jun 28, 2015)

Matt, I do not know how to thank you! This is perfect. Beside the solution itself I would like also to thank you for indicating me 3 new functions. 
I need to admit that at this moment DAX is not that intuitive for me as the normal Excel formulas, nevertheless I think is because I started the journey few days ago and I am willing to continue because amazed by the solutions I foresee. 
I am really grateful for your outstanding support.


----------



## Matt Allington (Jun 28, 2015)

You are welcome.  You should not feel bad that this is not immediately intuitive. This is very common. I teach DAX to Excel users and almost everyone is exactly the same.  My best advice is to do some formal learning. At a minimum you should read a good book. I recommend Rob Collie's hook as a good place to start. DAX Formula, Power Pivot, PowerPivot, DAX, Excel, Excel 2010, Excel 2013, Microsoft Excel, Microsoft Power Pivot

I am also writing a book but that won't be available until about Nov. Learn to Write DAX | Independent Publishers Group  You may already be an expert by then.


----------



## estgas (Jun 30, 2015)

Hi Matt, I am terribly sorry to ask you again because I am still not able to reproduce the Target column.
As you can see with material C the supplier can be X then Y than X again.
I tried to change the formula with LASTNOTBLANK but it does not work either.
Any idea how to build the Target column in DAX?

Date    ABC    CTRL    TARGET
1.1.2015    A    X    X
2.1.2015    A    _    X
3.1.2015    A    _    X
1.1.2015    B     _    
2.1.2015    B    Y    Y
3.1.2015    B    _    Y
4.1.2015    B    _    Y
3.1.2015    *C*     _    
4.1.2015    *C    X    X*
4.1.2015    *C*    _    *X*
4.1.2015    *C*    _    *X*
6.1.2015    *C    Y    Y*
6.1.2015    *C*    _    *Y*
7.1.2015    *C*    _    *Y*
8.1.2015    *C    X    X*
9.1.2015    *C*    _    *X*
1.2.2015    *C*    _    *X*
1.2.2015    *C*    _    *X*


----------



## Matt Allington (Jun 30, 2015)

OK, I couple of things here.  Firstly, this is a lesson on the importance of good test data.  You should always make sure your test data is a true representation of the variation that will occur in the real data.  Secondly, this is a significantly more difficult problem to solve in DAX.  LASTNONBLANK() works on the natural sort order of a column regardless of any other column and regardless of the order the data exists in the table.  So the CTRL column will always evaluate LASTNONBLANK(Data[CTRL],1) to be Y and not X, regardless of which order they appear in the column.  So once you hit 8 Jan, that will stop working.

I think the only way to do this is to add an ID column (a unique column that identifies each row in the order they are created.  I have just added and ID column in the test data table and added integers 1, 2, 3 etc.

Then I wrote this code.  This isn't pretty, and I don't know how fast it will be, but it works.


```
=LOOKUPVALUE (
    Data[CTRL],
    Data[ID], CALCULATE (
        MAX ( Data[ID] ),
        FILTER ( Data, Data[ABC] = EARLIER ( Data[ABC] ) ),
        FILTER ( Data, Data[ID] <= EARLIER ( Data[ID] ) ),
        FILTER ( data, NOT ( ISBLANK ( Data[CTRL] ) ) )
    )
)
```

FYI, the way this formula works is as follows.
1.  The 3 filter functions work together to filter the data table so that it contains values for the current ABC item, all Rows up to the current row, and only rows that are not blank.
2. CALCULATE then works out what the ID is for the last item in this filtered data table
3. Lookupvalue then uses this ID to find the value of TARGET for this ID and returns that as the answer

https://dl.dropboxusercontent.com/u/30711565/earlier2.xlsx


----------



## estgas (Jul 1, 2015)

Hi Matt, a couple of things also from my side:
- Many Many Thanks again for your support, thanks to your lessons I was able to reach what I was looking for.
- I do not have the index column in my input file but I built it in the model and now it works.
- You are right, next time I will try to be more complete with my test case.
- I ordered Rob Collie's and also another from two Italian guys and took note to look in November for yours.
Thanks again, Est Gas.


----------



## Matt Allington (Jul 1, 2015)

Both books are excellent.  I hope mine will be a good complement to them too. I recommend you Read Rob's book first as it is most appropriate for Excel users. Then read the book from the Italians. The second is technically much deeper, and it is a great natural progression from Rob's book. I have read chapters 7 and 8 at least 5 times in the Italians book


----------

