Forum Discussion

JLMJ's avatar
JLMJ
New Member
4 years ago
Solved

Getting consistent customers

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 transacti...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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

     

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago

 

# 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