Forum Discussion
JLMJ
4 years agoNew Member
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...
- 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 + 0Past 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
Jihwan_Kim
4 years agoSuper User
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