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

     

amitchandak's avatar
amitchandak
Super User
4 years ago

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-bd52912a5bd4