Firstnonblank measure help

[Anonymous]

Hello All,

I have a table with clients, date and deposit amount. What I want is to count how many clients made  first deposit and how much they deposited with the most efficient way. 

What I did:  

I created a new table with clients and the min date for each one. Then I run a lookup and I put "YES" if the FTD date match with the Deposit Date . Then I count and sum with filter "YES". 

FTD count = 5

FTD amount = 950

I suppose that there is a more efficient way to calculate the above with firstnonblank, without using additional table. Can anyone help me out? 

See below:

Table at the beginning.

Client	Deposit Date	Deposit Amount
a	01/01/2019	100
b	02/01/2019	200
c	03/01/2019	50
d	04/01/2019	400
e	05/01/2019	200
d	06/01/2019	50
a	07/01/2019	80
b	08/01/2019	800
c	09/01/2019	90
d	10/01/2019	150
a	11/01/2019	250
a	12/01/2019	300
b	13/01/2019	180
c	14/01/2019	90
d	15/01/2019	70
a	16/01/2019	50

Table Created:

Client	FTD Date
a	01/01/2019
b	02/01/2019
c	03/01/2019
d	04/01/2019
e	05/01/2019

Final table:

Original Data Table	Calculated Columns
Client	Deposit Date	Deposit Amount	FTD Date	FTD (YES/NO)
a	01/01/2019	100	01/01/2019	YES
b	02/01/2019	200	02/01/2019	YES
c	03/01/2019	50	03/01/2019	YES
d	04/01/2019	400	04/01/2019	YES
e	05/01/2019	200	05/01/2019	YES
d	06/01/2019	50	04/01/2019	NO
a	07/01/2019	80	01/01/2019	NO
b	08/01/2019	800	02/01/2019	NO
c	09/01/2019	90	03/01/2019	NO
d	10/01/2019	150	04/01/2019	NO
a	11/01/2019	250	01/01/2019	NO
a	12/01/2019	300	01/01/2019	NO
b	13/01/2019	180	02/01/2019	NO
c	14/01/2019	90	03/01/2019	NO
d	15/01/2019	70	04/01/2019	NO
a	16/01/2019	50	01/01/2019	NO

[Mariusz · Accepted solution]

Hi @Anonymous 

You can add this two columns to yourTable.

FTD = CALCULATE(
    MIN(yourTable[Deposit Date]),
    ALLEXCEPT(yourTable, yourTable[Client])
)

FTD (YES/NO) = 
IF(yourTable[Deposit Date] = yourTable[FTD], "YES", "NO")

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz]

Hi @Anonymous 

Actualy FIRSTDATE would be better as it will find first nonblanc date.

FTD = CALCULATE(
    FIRSTDATE(yourTable[Deposit Date]),
    ALLEXCEPT(yourTable, yourTable[Client])
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.