Count returning customers who have orders every month for X number of months

[aiweller]

Hi,

I'm seeking help to get the count of customers who have placed orders at least once every month for the past X months.

For example, I am hoping to capture the count of customers who have orders in October, November and December (past 2 months  and current month).

Any help and guidance is much appreciated.

Thanks,

Ashley

[littlemojopuppy · Accepted solution]

Hi!  Here's some DAX for something similar I did...

VAR Weeks = 2
VAR CurrentWeek =
    CALCULATETABLE (
        VALUES ( IdentityLog[IdentityId] ),
        DATESINPERIOD (
            'Calendar'[Date],
            DATEADD (
                LASTDATE ( 'Calendar'[Date] ),
                ( Weeks * 7 ) + 1,
                DAY
            ),
            7,
            DAY
        )
    )
VAR PreviousWeek =
    CALCULATETABLE (
        VALUES ( IdentityLog[IdentityId] ),
        DATESINPERIOD (
            'Calendar'[Date],
            DATEADD (
                LASTDATE ( 'Calendar'[Date] ),
                ( ( Weeks - 1 ) * 7 ) + 1,
                DAY
            ),
            7,
            DAY
        )
    )
VAR TwoWeeksAgo =
    CALCULATETABLE (
        VALUES ( IdentityLog[IdentityId] ),
        DATESINPERIOD (
            'Calendar'[Date],
            DATEADD (
                LASTDATE ( 'Calendar'[Date] ),
                ( ( Weeks - 2 ) * 7 ) + 1,
                DAY
            ),
            7,
            DAY
        )
    )
RETURN

COUNTROWS (
    CALCULATETABLE (
        VALUES(IdentityLog[IdentityId]),
        CurrentWeek,
        PreviousWeek,
        TwoWeeksAgo
    )
)

The measure above is intended to get the count of users who have logged in each week for three consecutive weeks.  Replace my logic for weeks with current month, prior month and two months ago (PARALLELPERIOD() should make that very easy!).

[StefanoGrimaldi · Accepted solution]

hey this would do the trick (a measure): 

Measure =

var a = 0

var b = 365

return

CALCULATE(DISTINCTCOUNT('orders report'[month value column]),(TODAY()-'Date Dim Table'[Date])<b,(TODAY()-'Date Dim Table'[Date])>=a)

this will determine if it its between last 365 days (1 year) and if so count by each different moth it exists on it, when applied to a table the filter will kick in with the desire visual)

note I use a date dimensional table, you can do it without it using the date in the fact table also. 

Result of it: 

if this was of help for your need let us know makring as soluction. 

[aiweller]

Thank you all for the solutions. I spent the last 2 weeks trying to find a solution myself on google search and got a solution within 1 day in this community. :). You guys are great!

[amitchandak · Accepted solution]

@aiweller , This measure can give distinct month has sales , with help from date table

Example

Rolling 3 = CALCULATE(distinctcount('Date'[Month-Year]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0),-3,MONTH), not(isblank(Sales[Sales Amount])))

Count of customers having entry in three month

new Measure = countx(filter(Summarize(Sales, sales[Customer], "_1",[Rolling 3]),[_1]=3),[Customer])

[StefanoGrimaldi · Accepted solution]

hey this would do the trick (a measure): 

Measure =

var a = 0

var b = 365

return

CALCULATE(DISTINCTCOUNT('orders report'[month value column]),(TODAY()-'Date Dim Table'[Date])<b,(TODAY()-'Date Dim Table'[Date])>=a)

this will determine if it its between last 365 days (1 year) and if so count by each different moth it exists on it, when applied to a table the filter will kick in with the desire visual)

note I use a date dimensional table, you can do it without it using the date in the fact table also. 

Result of it: 

if this was of help for your need let us know makring as soluction. 

[littlemojopuppy · Accepted solution]

Hi!  Here's some DAX for something similar I did...

VAR Weeks = 2
VAR CurrentWeek =
    CALCULATETABLE (
        VALUES ( IdentityLog[IdentityId] ),
        DATESINPERIOD (
            'Calendar'[Date],
            DATEADD (
                LASTDATE ( 'Calendar'[Date] ),
                ( Weeks * 7 ) + 1,
                DAY
            ),
            7,
            DAY
        )
    )
VAR PreviousWeek =
    CALCULATETABLE (
        VALUES ( IdentityLog[IdentityId] ),
        DATESINPERIOD (
            'Calendar'[Date],
            DATEADD (
                LASTDATE ( 'Calendar'[Date] ),
                ( ( Weeks - 1 ) * 7 ) + 1,
                DAY
            ),
            7,
            DAY
        )
    )
VAR TwoWeeksAgo =
    CALCULATETABLE (
        VALUES ( IdentityLog[IdentityId] ),
        DATESINPERIOD (
            'Calendar'[Date],
            DATEADD (
                LASTDATE ( 'Calendar'[Date] ),
                ( ( Weeks - 2 ) * 7 ) + 1,
                DAY
            ),
            7,
            DAY
        )
    )
RETURN

COUNTROWS (
    CALCULATETABLE (
        VALUES(IdentityLog[IdentityId]),
        CurrentWeek,
        PreviousWeek,
        TwoWeeksAgo
    )
)

The measure above is intended to get the count of users who have logged in each week for three consecutive weeks.  Replace my logic for weeks with current month, prior month and two months ago (PARALLELPERIOD() should make that very easy!).