IF & Time

[majdkaid22]

Hi, 

am trying to distinguish the "New" from "Additional" Dep Amounts made by clients. 

In Excel, I use IF(MATCH as seen below, where it check if this rows exist in the previous rows (not a solid formula, but doing the job)

I cannot really figure out how to create a similar formula in M or Power BI desktop

my data looks like the below 

[greggyb · Accepted solution]

There's no concept of cell addressing in Power Query or Tabular. Row numbers are displayed for convenience, but are explicitly not a reference to the data in that row, unlike Excel. You can only refer to a specific row if you know a combination of fields that combine to uniquely identify that row.

You can refer to a subset of rows by defining a value or set of values that uniquely distinguish those rows from the others in the table. Essentially, every row reference you make must be possible without depending on the physical presence of one row being above or below that of another.

That being said, you've got a date field defined which should do just fine for imposing an order upon the table. Here's some DAX:

NewField =
IF(
    ISEMPTY(
        CALCULATETABLE(
            'Table'
            ,ALLEXCEPT( 'Table', 'Table'[Name] )
            ,'Table'[Date] < EARLIER( 'Table'[Date] )
        )
    )
    ,"New"
    ,"Additional"
)

IF() is trivial. ISEMPTY() tests whether the table passed as argument to it has no rows.

CALCULATETABLE() evaluates a table in  a context we can define. First it adds all of the field values from the current row to filter context. We clear that with ALLEXCEPT(), which says remove all context except from the field(s) I name. We keep context on [Name] from the current row.

Then we check that [Date] is less that the value on the current row. Row context is not the simplest concept to wrap your mind around. When we are adding a calculated column to a table, the formula we define is evaluated in that table's row context, i.e. once per row. Thus we have row context from 'Table' on the outside of our IF(). This is row context 1, RC1. Then, within our CALCULATETABLE() function, the filter argument on 'Table'[Date] is evaluated in a new, nested row context (also on 'Table'). This is row context 2, RC2. "'Table'[Date]" is evaluated in RC2, but must be compared to RC1. EARLIER() allows us to reach out of the inner RC2, and grab the value of [Date] from RC1.

[majdkaid22]

@greggyb That absolutely worked perfect!! 

Am still going through your explanation to fully understand the logic, and I truly appreciate the depth of your reply. 

[majdkaid22]

Hi @greggyb

You greatly assisted me in the above formula a while ago, which is working great for me. 

IF(
    ISEMPTY(
        CALCULATETABLE(
            'Table'
            ,ALLEXCEPT( 'Table', 'Table'[Name] )
            ,'Table'[Date] < EARLIER( 'Table'[Date] )
        )
    )
    ,"New"
    ,"Additional"
)

I've been trying to add another 2 arguments, but so far couldn't achieve what I wanted which will look up the [Name] in previous dates and if no Transaction is found, then it's "New" otherwise it's "Additional"

I was able to create another measure to filter out Transactions that greater than $200 (our benchmark for a "New") but I have encountered some scenarios where Clients could make a test Transactions for below $200 and then they follow it in the same day or same month with a greater deposit of $200. 

Obviously these transactions are flagged as "additional"

What I would like to have is: 

_______ 

1- IF meAccountTransaction'[Name] does not exist prior to this month 

&

2- IF meAccountTransaction'[Sum Deposits] per  meAccountTransaction'[Name] during the current month is greater than 200

Then

"New", "additional"

_______

Any help would be highly appreciated, as I got stuck with this one for quite few days without luck 🙂

Thanks,

Majd