How to apply a filter based on the current row

Question

I'm building a calculated column that needs to sum all the rows in the same table that share a few common properties and have a greater date value.

I know I need to use calculate to break the filter context, but I'm not sure how to reference the row being calculated vs the table of the same name inside the calculate function. In Sql, this would be done as a self join with two different aliases for the same table, what is the DAX equivalent?

SQL pseudo code:

select
t1.Name
,sum(t2.a)
from table t1
inner join table t2 on t1.b = t2.b
    and t1.c < t2.c
group by t1.name

DAX (how do I correctly reference outer row vs inner table?):

calculate(sum(table[a]), 
        filter(all(table), table[b] = table[b])
)

In the past, EARLIER function was used to refer to the current row. Lately, a typical pattern is to store current row's value in a variable and then use it inside CALCULATE. If you want more specific help, post your data sample and describe a desired outcome. — RADO

Neil P Neil P · Accepted Answer · 2020-02-26T15:30:07

As suggested by @RADO, the earlier function does this (but you might not guess that from the name!).

Using my previous example, it looks like this:

calculate(sum(table[a]), 
    filter(all(table), table[b] = earlier(table[b]))
)

How to apply a filter based on the current row

1 Answers