DAX Earlier function

[estgas]

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]

Yes, just wrap my code in an if statement.

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

 

[estgas]

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]

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]

Try this

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

 

[estgas]

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]

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]

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]

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]

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]

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