DAX Earlier function

E

estgas

New Member

Joined

Jan 2, 2015

Messages

24

• Jun 26, 2015

• #2

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

 

Upvote 0

E

estgas

New Member

Joined

Jan 2, 2015

Messages

24

• Jun 26, 2015

• #3

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

 

Upvote 0

M

Matt Allington

MrExcel MVP

Joined

Dec 18, 2014

Messages

1,516

• Jun 27, 2015

• #4

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?

 

Upvote 0

M

Matt Allington

MrExcel MVP

Joined

Dec 18, 2014

Messages

1,516

• Jun 27, 2015

• #5

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

 

Upvote 0

E

estgas

New Member

Joined

Jan 2, 2015

Messages

24

• Jun 27, 2015

• #6

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

 

Last edited: Jun 27, 2015

Upvote 0

M

Matt Allington

MrExcel MVP

Joined

Dec 18, 2014

Messages

1,516

• Jun 27, 2015

• #7

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

 

Upvote 0

E

estgas

New Member

Joined

Jan 2, 2015

Messages

24

• Jun 27, 2015

• #8

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

 

Upvote 0

M

Matt Allington

MrExcel MVP

Joined

Dec 18, 2014

Messages

1,516

• Jun 27, 2015

• #9

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)

Code:

=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

 

Upvote 0

E

estgas

New Member

Joined

Jan 2, 2015

Messages

24

• Jun 27, 2015

• #10

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 ?

 

Upvote 0