Forum Discussion
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 |
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.
2 Replies
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.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.