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.

 

[estgas]

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]

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]

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]

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

 

[estgas]

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]

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

 

[estgas]

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]

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]

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 ?