topic Re: Slicer for New and Lost Customers in DAX Commands and Tips

Slicer for New and Lost Customers

TimmK — Thu, 24 Feb 2022 08:32:52 GMT

For simplicity I have the tables FACT Sales, DIM Customer and DIM Date. My report table is based on this and has columns for each customer and shows the sales sum for each year (2019-2022).

Definition new customer = Sales in selected year, but no sales in previous year; For example sales>0 in 2021, but no sales in 2020.

Definition lost customer = No sales in selected year, but sales in previous year; For example no sales in 2021, but sales>0 in 2020.

Now I want a slicer that contains the values N2019, N2020, N2021, N2022, L2019, L2020, L2021, L2022. N = new customer; L = lost customer. For example, when I select the value N2020 I want to see only those customer in the report table with sales>0 in 2020, but no sales in 2019.

Is this possible? If yes, how? If not, what would be other possibilities to reach my goal?

Re: Slicer for New and Lost Customers

amitchandak — Thu, 24 Feb 2022 12:31:57 GMT

TimmK , You have to create an independent table with values New Customer and Lost Customer and Retained Customer

Example measure to check sales

YTD = calculate([Sales],datesYtd('Date'[Date])) LYTD = calculate([Sales],DATESYTD(DATEADD('Date'[Date],-1,MONTH)))

Type =
Switch( True(),
ISBLANK([YTD]) && not(ISBLANK([LYTD]) , "Lost Customer",
ISBLANK([LYTD]) && not(ISBLANK([YTD]) , "New Customer",
"Retained Customer"
)

Assume Table with three value as Bucket

New measure =
Countx(filter(VALUES(Customer[Customer Id]) , [Type] = max(Bucket[Type])), [Customer ID])

New measure sales
=
Countx(filter(VALUES(Customer[Customer Id]) , [Type] = max(Bucket[Type])), [sales])

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Re: Slicer for New and Lost Customers

TimmK — Fri, 25 Feb 2022 07:27:31 GMT

Thank you, but it seems this is not exactly the solution I am looking for. I want a slicer that contains the values N2019, N2020, N2021, N2022, L2019, L2020, L2021, L2022. For example, when I select N2020 my report table that lists all customers should be reduced to only those customers with sales in 2020, but no sales in 2019.

Re: Slicer for New and Lost Customers

v-cazheng-msft — Mon, 28 Feb 2022 09:38:24 GMT

Hi TimmK ,

You may try this solution.

1 Create a table containing the values N2019, N2020, N2021, N2022, L2019, L2020, L2021, L2022

2 Create a Measure

New Or Lost Customers = VAR selectedVal = SELECTEDVALUE ( SlicerTable[SelectedValue] ) VAR NL = LEFT ( selectedVal, 1 ) VAR Year_ = VALUE ( RIGHT ( selectedVal, 4 ) ) VAR Cur_Year = CALCULATE ( SUM ( 'FACT Sales'[Amount] ), FILTER ( 'FACT Sales', YEAR ( 'FACT Sales'[Date] ) = Year_ ) ) VAR Pre_Year = CALCULATE ( SUM ( 'FACT Sales'[Amount] ), FILTER ( 'FACT Sales', YEAR ( 'FACT Sales'[Date] ) = Year_ - 1 ) ) VAR IsCurBlank = IF ( ISBLANK ( Cur_Year ), 0, 1 ) VAR IsPreBlank = IF ( ISBLANK ( Pre_Year ), 0, 1 ) VAR CountForNew = CALCULATE ( DISTINCTCOUNT ( 'FACT Sales'[Customer Key] ), FILTER ( 'FACT Sales', IsCurBlank = 1 && IsPreBlank = 0 ) ) VAR CountForLost = CALCULATE ( DISTINCTCOUNT ( 'FACT Sales'[Customer Key] ), FILTER ( 'FACT Sales', IsCurBlank = 0 && IsPreBlank = 1 ) ) RETURN IF ( NL = "N", CountForNew, CountForLost )

3 Use the Measure above as the visual filter of the Table visual

Then, the result will look like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

Re: Slicer for New and Lost Customers

Whitewater100 — Mon, 28 Feb 2022 14:49:16 GMT

Hello:

There are a handul of steps needed.

First build a slicer table.

I named it SelectedMeasure.

Then create New & Lost Measures for each of the eight categories. I'll paste a new and lost one for 2021 next:

Example:

New Customers(2021) =

var _customer =

CALCULATETABLE( VALUES('Internet Sales'[CustomerKey]),

FILTER(ALL('Date'),

'Date'[CalendarYear] = 2021))

var _priorcustomers = CALCULATETABLE( VALUES('Internet Sales'[CustomerKey]),

FILTER(ALL('Date'),

'Date'[CalendarYear] = 2020))

return

COUNTROWS(

EXCEPT(_customer,_priorcustomers)

)

Example:

Lost Customers(2021) =

var _customer =

CALCULATETABLE( VALUES('Internet Sales'[CustomerKey]),

FILTER(ALL('Date'),

'Date'[CalendarYear] = 2021))

var _priorcustomers = CALCULATETABLE( VALUES('Internet Sales'[CustomerKey]),

FILTER(ALL('Date'),

'Date'[CalendarYear] = 2020))

return

COUNTROWS(

EXCEPT(_priorcustomers, _customer)

)

Then put slicer on page.

Then put your final measure to coincide with your slicer selection:

Switch Measure =

var Selection = SELECTEDVALUE('SelectedMeasure'[Title])

return

SWITCH(

Selection,

"N2019", [New 2019],

"N2020", [New 2020],

"N2021", [New Customers(2021)],

"N2022", [New Customers 2022],

"L2019", [Lost 2019],

"L2020", [Lost 2020],

"L2021", [Lost Customers(2021)],

"L2022", [Lost 2022])

I guess you have data from 2018 to do all of this.

Re: Slicer for New and Lost Customers

tamerj1 — Tue, 01 Mar 2022 04:11:53 GMT

Hi TimmK
What about the lost sales? Are you going to consider the previous sales as the lost value for the selected year?

Re: Slicer for New and Lost Customers

tamerj1 — Tue, 01 Mar 2022 05:03:15 GMT

TimmK
Refer to sample file with the solution https://www.dropbox.com/t/URCvZUxrO8tQbALX
One way to do that is by creating a calculated column

New/Leaving/Continuing = VAR CurrentCustomer = Sales[Customer Key] VAR CurrentYear = YEAR ( Sales[Date] ) VAR PreviousYearSales = COUNTROWS ( FILTER ( Sales, Sales[Customer Key] = CurrentCustomer && YEAR ( Sales[Date] ) = CurrentYear - 1 ) ) VAR NextYearSales = COUNTROWS ( FILTER ( Sales, Sales[Customer Key] = CurrentCustomer && YEAR ( Sales[Date] ) = CurrentYear + 1 ) ) RETURN IF ( PreviousYearSales = 0, "N" & YEAR ( Sales[Date] ), IF ( YEAR ( Sales[Date] ) + 1 <= YEAR ( NOW() ), IF ( NextYearSales = 0, "L" & YEAR ( Sales[Date] ) + 1, "C" & YEAR ( Sales[Date] ) ), "C" & YEAR ( Sales[Date] ) ) )

The sales that are neither new nor lost are considered continuing sales. The report shall look like this

Please let me know if this answers your query or if you need any further modifications.