topic Re: Getting consistent customers in DAX Commands and Tips

Getting consistent customers

JLMJ — Mon, 08 Aug 2022 02:23:26 GMT

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

Re: Getting consistent customers

amitchandak — Mon, 08 Aug 2022 03:03:38 GMT

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

Re: Getting consistent customers

Jihwan_Kim — Mon, 08 Aug 2022 03:47:49 GMT

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

Re: Getting consistent customers

CNENFRNL — Mon, 08 Aug 2022 13:13:52 GMT

# 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