Getting consistent customers

[JLMJ]

Hi!

Im a beginner in PowerBI.

I need help in creating this view.

Suppose I have this transactional dataset.

What I want to get are the counts of customers with consistent transaction for the past 3 months, 6 months, and even 12 months (relative to the month of reference and category)

Here's the result I would want to achieve:

I think this can be resolved through DAX but let I am open with any suggestions.

Thanks!!

[CNENFRNL]

# Detension = 
VAR __n = MAX( SPAN[Consecutive] )
VAR __p = DATESINPERIOD( DATES[Date], MAX( DATES[Date] ), - __n, MONTH )
RETURN
    COUNTROWS(
        FILTER(
            VALUES( DATA[Cust_ID] ),
            CALCULATE( DISTINCTCOUNT( DATES[Yr-Mn] ), CALCULATETABLE( DATA, __p ) ) = __n
        )
    ) + 0

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

Past 3 months consistent customers count: = 
VAR _selectedcategory =
    VALUES ( Category[Category] )
VAR _pastmonthsnumberselect = 3
VAR _selectedmonthenddate =
    MAX ( 'Calendar'[End of Month] )
VAR _startdate =
    EOMONTH ( _selectedmonthenddate, - _pastmonthsnumberselect ) + 1
VAR _monthlycalendartable =
    SUMMARIZE (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= _selectedmonthenddate
                && 'Calendar'[Date] >= _startdate
        ),
        'Calendar'[Month-Year]
    )
VAR _newtable =
    FILTER (
        SUMMARIZE (
            ADDCOLUMNS (
                FILTER (
                    SUMMARIZE (
                        ALL ( Data ),
                        Category[Category],
                        Customer[Cust_ID],
                        'Calendar'[Month-Year]
                    ),
                    'Calendar'[Month-Year]
                        IN _monthlycalendartable
                        && Category[Category] IN _selectedcategory
                ),
                "@amountsum", CALCULATE ( SUM ( Data[Amount] ) )
            ),
            Customer[Cust_ID],
            'Calendar'[Month-Year],
            [@amountsum]
        ),
        [@amountsum] <> 0
    )
VAR _groupbycustomer =
    FILTER (
        GROUPBY (
            _newtable,
            Customer[Cust_ID],
            "@monthcount", SUMX ( CURRENTGROUP (), 1 )
        ),
        [@monthcount] >= _pastmonthsnumberselect
    )
VAR _result =
    COUNTROWS ( _groupbycustomer )
RETURN
    _result + 0

Past 6 months consistent customers count: = 
VAR _selectedcategory =
    VALUES ( Category[Category] )
VAR _pastmonthsnumberselect = 6
VAR _selectedmonthenddate =
    MAX ( 'Calendar'[End of Month] )
VAR _startdate =
    EOMONTH ( _selectedmonthenddate, - _pastmonthsnumberselect ) + 1
VAR _monthlycalendartable =
    SUMMARIZE (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= _selectedmonthenddate
                && 'Calendar'[Date] >= _startdate
        ),
        'Calendar'[Month-Year]
    )
VAR _newtable =
    FILTER (
        SUMMARIZE (
            ADDCOLUMNS (
                FILTER (
                    SUMMARIZE (
                        ALL ( Data ),
                        Category[Category],
                        Customer[Cust_ID],
                        'Calendar'[Month-Year]
                    ),
                    'Calendar'[Month-Year]
                        IN _monthlycalendartable
                        && Category[Category] IN _selectedcategory
                ),
                "@amountsum", CALCULATE ( SUM ( Data[Amount] ) )
            ),
            Customer[Cust_ID],
            'Calendar'[Month-Year],
            [@amountsum]
        ),
        [@amountsum] <> 0
    )
VAR _groupbycustomer =
    FILTER (
        GROUPBY (
            _newtable,
            Customer[Cust_ID],
            "@monthcount", SUMX ( CURRENTGROUP (), 1 )
        ),
        [@monthcount] >= _pastmonthsnumberselect
    )
VAR _result =
    COUNTROWS ( _groupbycustomer )
RETURN
    _result + 0

[amitchandak]

@JLMJ , Have date table, and have month year in your table/sales table

Sales Month = format([Date], "YYYYMM")

Rolling 3= CALCULATE(distinctcount(Sales[Sales Month]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Continue in the last three months

countx(filter(Values(Sales[Customer]) , [Rolling 3]=3) , [Customer])

Same way change rolling to 6, 12 to get the number

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...