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.
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())
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.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps