cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Firstnonblank measure help

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Firstnonblank measure help

Hi @mdemos02

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.

2 REPLIES 2
Super User

## Re: Firstnonblank measure help

Hi @mdemos02

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.

Super User

## Re: Firstnonblank measure help

Hi @mdemos02

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.

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)