how to count customers transactions that, defined percent of transactions happened every 1-10 days ?

[Anonymous]

Hi

Can anyone help me to solve my problem?!

assume that i have about 4000 customers and a table of daily transactions that has more than 15m rows, how can I find count of customers that e.g. 80% of his transactions accure every 1-10 days. Its important for me that this measue is as fast as possible, I think that I cant calculate lags by column, becasue end user should be able to see the result by one or more than one product.

Many Thanks

[Anonymous]

It's not clear what you want to calculate. You need to give a clear example using simple data. And cover some decent cases. Thanks.

[Anonymous]

It's clear. I just sent my post almost simultaneously with you... so couldn't see the example.

[Anonymous]

I post an exmple. not clear yet?

[Anonymous]

@amitchandak & @Greg_Deckler 

for example i have a daily transactions table like this:

cutomer	product	date
A	P1	01/01/2020
A	P1	09/01/2020
A	P2	17/01/2020
A	P1	25/01/2020
A	P2	01/02/2020
A	P2	18/02/2020
B	P1	01/01/2020
B	P1	10/01/2020
B	P2	01/02/2020
B	P2	10/02/2020

Q : How many customer have 80 % of their transactions in 1-10 days?

Answer: - A have 5 transaction (we can dismiss first transaction in total count because lag is null) and 4 of them has datediff<10 so (4/5)*=80 

             - B have 3 transaction and 2 of them has datediff<10 so (2/3)*100=66

so my measure should return "1" in this example

thanks

[Greg_Deckler]

@Anonymous - I think if you create a new column like this:

Days = 
  VAR __Previous = MAXX(FILTER('Table',[cutomer] = EARLIER('Table'[cutomer]) && [date] < EARLIER('Table'[date])),[date])
RETURN
  ([date] - __Previous) * 1.

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

Any way, I think once you do this the rest becomes easy. You could now create a measure like the following:

Measure =
  VAR __Table = 
    SUMMARIZE('Table',[cutomer],"percent",
      DIVIDE(
        COUNTROWS(FILTER('Table',[Days] < 10)),COUNTROWS('Table')
      )
    )
RETURN
  COUNTROWS(FILTER(__Table,[percent] >= .8))

[Anonymous]

@Greg_Deckler 

Thanks for your feedback, but I think, I cant use column calculation because I have a product slicer and user can choose any product, in this example assume that user select "P1" and "P2".

e.g. in this example if user select just "P2" then...

A : has 3 transaction but non of them is between 1-10 days

B : has one transaction and its in 10 days

so measure return 1 (but this time its B)

[amitchandak]

@Anonymous , I created a solution too. But I think the solution from @Greg_Deckler  should work for you. In any case, as I have created, please find the link

https://www.dropbox.com/s/nu7m2teiq2z03qs/in%20first%20few%20days%201.pbix?dl=0

[Anonymous]

@Anonymous

Sorry but I can't send private messages 😞 For some reason this functionality is not available to me.

Apart from that, if any of the answers has helped you with a solution, please mark it as the solution. This should help others later on. Thanks.

[Greg_Deckler · Accepted solution]

@Anonymous - Just use ADDCOLUMNS to wrap the SUMMARIZE and move the column calculation to the measure.

[Anonymous]

This works for me, Thanks @Greg_Deckler 🙂

@Anonymous I think your solution can be a good solution too, but for some reason I cant modify and test it till now but I will test this solution too and compare it with Greg's and share the result soon, thanks

[Anonymous]

@Anonymous

COALESCE(a, b) does just one thing: it returns a if a is not BLANK, and b if it is. So, you can use this expression if you can't use COALESCE: IF( a = BLANK(), b, a )

[Anonymous]

And remember that BLANK() in DAX is treated as 0 in comparisons with numbers, and as the empty string "" when compared to strings. If you want to detect BLANK() and not 0, you have to use the ISBLANK(*) function.

[Anonymous]

You can try this but since I have no time to generate enough data... you'll have to play with this to get the speed you want. Just try to tweak this formula.

// Assuming you've got:
// Customer - dimension that stores custs
// Product  - dimension that stores products
// Date     - dimension that stores dates (proper date table)
// Sales    - fact table that you've shown
// Assumption 2 is that the dates for a single customer
// can't be the same for 2 different transactions.


// This is a measure that will calc
// what you want for any selection
// from the dimensions. Not sure how fast
// it's going to be since I don't have time
// to generate enough data. But it's a start
// in any case.

[80%+ # Cust (1-10)] =
var __custWithDateCount =
    ADDCOLUMNS(
        SUMMARIZE(
            Sales,
            Customer[CustomerID]
        ),
        "@DateCount",
            CALCULATE(
                DISTINCTCOUNT(
                    Sales[Date]
                )
            )
    )
var __custDate =
    SUMMARIZE(
        Sales,
        Customer[CustomerID],
        Date[Date]
    )
var __custWithRelevantDayCounts =
    GROUPBY(
    
        FILTER(
            ADDCOLUMNS(
                __custDate,
                "@DiffInDays",
                    var __cust = Customer[CustomerID]
                    var __date = Date[Date]
                    return
                        __date
                        - coalesce(
                            MAXX(
                                filter(
                                    __custDate,
                                    Customer[CustomerID] = __cust
                                    &&
                                    Date[Date] < __date
                                ),
                                Date[Date]
                            ),
                            __date
                        )
            ),
            [@DiffInDays] > 0 // want to get rid of the first date
            &&
            [@DiffInDays] < 10
        ), // end of FILTER
        
        // start of GROUPBY
        Customer[CustomerID],
        "@DateCount", COUNTROWS( CURRENTGROUP() )
    )
var __result =
    COUNTROWS(
        FILTER(
            // Structure [CustomerID | @DateCount]
            __custWithRelevantDayCounts,
            var __currentCustomer = Customer[CustomerID]
            var __dayCount =
                // I use MAXX but for the filter
                // there will always be only 1 row.
                MAXX(
                    filter(
                        __custWithDateCount,
                        Customer[CustomerID] = __currentCustomer
                    ),
                    [@DateCount]
                )
            return
                [@DiffInDays] >= .8 * __dayCount
        )
    )
return
    __result

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

[Greg_Deckler]

@Anonymous Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.