Find out which buying client bought previous month

[ny326n]

Hi all!

I have a table containing all clients who ever placed an order. The coulmns are:

Date, Client ID, Order ID

What I want to find out is which clients who placed an order in current month, also placed an order in previous month. Or more precisely, Percent of buying customers in current month who bought last month. Does anyone have a suggestion on how to do this?

Thank you in advance!

[v-sihou-msft · Accepted solution]

@ny326n

For your requirement, you can create a full calendar table, then build relationship to your table on Date column. Then you can add a "Tag" column to tag if an users has order in previous month:

IsActive =
IF (
    CALCULATE (
        COUNTROWS ( Table ),
        PREVIOUSMONTH ( Table[Date] ),
        ALLEXCEPT ( Table, Table[ClientID] )
    )
        > 0,
    1,
    0
)

Then you can easily calculate active users percentage with above column:

Active User Pct=
CALCULATE (
    DISTINCTCOUNT ( Table[ClientID] ),
    ALLEXCEPT ( Table, Table[Month] ),
    FILTER ( Table, Table[IsActive] = 1 )
)
    / CALCULATE (
        DISTINCTCOUNT ( Table[ClientID] ),
        ALLEXCEPT ( Table, Table[Month] )
    )

Regards,

[ny326n]

Thank you for this Simon!