Need help optimizing measure

[sonicfish]

Hello,

I have a measure for calculating repeating customers.  Definition of repeating customer is someone who made a purchase this period (for example, today), and also made two or more purchases in the last 180 days.  After the report is published on PowerBI service, the measure runs fine and returns result as long as the date range is set to one year, but anything move than that will return an error about the consumed memory exceeding 1024MB limit.  My sales order table has about 1.8 million row.  I need some help with optimizing the measure.  Thanks!

The [Date Repeating Customer] measure in the Repeating Customers measure below is just:

CALCULATE(min(DateTable[CalendarDate]) )

[VahidDM]

Hi @sonicfish 

Try this:

Measure =
VAR _Start =
    CALCULATE ( MIN ( DateTable[CalendarDate] ) )
VAR _Finish = _Start - 180
VAR _CTP =
    CALCULATETABLE (
        VALUES ( [email] ),
        FILTER ( DateTable, DateTable[CalendarDate] = _Start )
    )
VAR _NT =
    ADDCOLUMNS (
        _CTP,
        "TT",
            CALCULATE (
                COUNTROWS ( vCustomerOrders ),
                FILTER (
                    ALL ( DateTable ),
                    DateTable[CalendarDate] >= _Start
                        && DateTable[CalendarDate] <= _Finish
                )
            )
    )
VAR _FNT =
    FILTER ( _NT, [TT] >= 2 )
RETURN
    COUNTROWS ( _FNT )

It would be better if you share a sample of your data here in a text format.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[sonicfish]

Hi @VahidDM,

Thanks for your recommedation!  I just tried your code but it's not returning anything.  Here is the data in my vCustomerOrders table looks like:

If the date on my report is set to 3/14/2022, CustomerC and CustomerD will be repeating customer because the first order the date of their previous order is within the last 180 days.  CustomerB is not repeating because he has only one order.  CustomerA is also not repeating because the previous order date is more than 180 days ago.

[VahidDM]

@sonicfish Can you copy and past your sample data here as a text to be able to cupy and past them into PBI?

Also, can you let us know how do you want to show the result?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/