10-17-2018 02:24 AM - edited 10-17-2018 02:26 AM
Couldn't find anything that matches what I'm looking for and I couldn't figure a good way to do this.
What I am trying to do is a second time deposit report - in order to see how many clients/percentage of clients has made a second depsit. In order to achieve that, I need a way to count only deposits that appear the 2nd time according to a specific identifier. In our case, our identifier is CustomerID. I will illustrate:
CustomerID | Amount | is_second_time 4 | $1000 | FALSE (1st instance, so FALSE). 5 | $2000 | FALSE (1st instance, so FALSE). 4 | $1500 | TRUE (2nd instance, so TRUE). 4 | $4000 | FALSE (3rd instance, so FALSE, etc..). 5 | $2000 | TRUE (2nd instance, so TRUE). 5 | $2000 | FALSE (3rd instance, so FALSE, etc..).
I think it's pretty clear, just need to mark 2nd instances of transactions according to CustomerID so I can use it in a measure to filter out only those with is_second_time = TRUE. (is_second_time is basically the column that will change to either TRUE or FALSE.)
Solved! Go to Solution.
10-17-2018 05:25 AM
10-17-2018 03:14 AM
try to add index column in table in query editor
and use dax formule
secondValue = IF(COUNTROWS(FILTER('table';EARLIER('table'[CustomerID])='table'[CustomerID]&&EARLIER('table'[Index])>='table'[Index]))=2;TRUE();FALSE())
10-17-2018 03:59 AM - edited 10-17-2018 04:00 AM
It partly worked, but not exactly... I forgot to mentioon that the CustomerID is random and not ascending. So it can sometimes see a transaction that was created on the 15/01/2018 15:15 as the second one (TRUE) and a transaction that was created on the 15/01/2018 15:16 as the first one (FALSE).
So that way lets say we got transactions with the same CustomerID with the dates:
16/01/2018 15:15 -> FALSE 12/01/2018 15:15 -> FALSE 25/12/2017 15:15 -> FIRST ONE (FALSE) 05/01/2018 15:15 -> ACTUAL SECOND ONE (TRUE) 17/01/2018 15:15 -> FALSE
Right now I think it's going randomly, like:
16/01/2018 15:15 -> FALSE 12/01/2018 15:15 -> FALSE 25/12/2017 15:15 -> FALSE (CORRECT) 05/01/2018 15:15 -> FALSE -> (this one should be true because it's the actual second transaction from the customer). 17/01/2018 15:15 -> TRUE -> (this one should be false although it is a second instance, but it is not the actual second transaction from the customer)
Is there a way to make it apply as in the oldest, actual second transaction? Let's say the date column is named created_on.
10-17-2018 05:25 AM