Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Calculated Column

Hi, this is my request:   I need to find [Date] of the previous row by date (highest date less than the current date) adding these conditions:  [First_Parameter] of this row must be equal to [Fir...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try a new column like, you can add condition using && (and) || or , inside the same filter

     

    Last Date Time = maxx(filter(table,table[Date Time]<earlier(table[Date Time]) && table[param1] =earlier(table[param1]) && table[A] =earlier(table[A])),table[Date Time])
    Max value = maxx(filter(table,table[team] =earlier(table[team]) && table[A] =earlier(table[A])),table[B])

rainer1's avatar
rainer1
Resolver III
6 years ago

Hi Anonymous,

 

the earlier function is really slow and memory intensive. There are a few possible solutions:

 

At first build up a Index column based on the date by this Dax

Index = 
VAR CurrentObjectID = 'My_Table'[object_id]
RETURN
RANKX (
FILTER (
'My_Table'; 
'My_Table'[object_id] = CurrentObjectKey
); 
'My_Table'[DATE]; ; ASC; Dense
)

  if you don't have a object_id you can use this 

Index = 
RANKX (
'My_Table'; 
'My_Table'[DATE]; ; ASC; Dense
)

or build an index in Power Query Editor if you don't want to sort it by date.

May be you already have a index column then leave this step out.

 

Then build up the variable by this:

 

new_column =
var prev_number = 'My_Table'[index] -1
varx = LOOKUPVALUE('My_Table'[Date]; 'My_Table'[First_Parameter];'My_Table'[First_Parameter];'My_Table'[index];prev_number)

 this will Lookup for the date of the previous column by the index.

 

-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!