Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
05-02-2017 22:15 PM - last edited 05-02-2017 22:16 PM
Name:
New customers
Description:
Calculate a measure filtering only the new customers in the selected period
Parameters:
Name: Base value
Tooltip: The value you want to calculate
Type: Numerical field / measure
Name:FactTable
Tooltip: The table containing the data used in Base value
Type: Table
Name: DateOfTransaction
Tooltip: The column containing the date of the transaction in the fact table (see FactTable)
Type: Column
Name: DateColumn
Tooltip: The column Date in the Date table
Type: Column
Name: CustomerKeyColumn
Tooltip: The column containing the customer identified in the customers table
Type: Column
{Base value} New Customers =
VAR FirstPurchaseCustomers =
ADDCOLUMNS (
ALL ( {CustomerKeyColumn} ),
"DateOfFirstBuy", CALCULATE ( MIN ( {DateTransaction} ), ALLEXCEPT ( {FactTable}, {CustomerKeyColumn} ) )
)
VAR NewCustomers =
FILTER (
FirstPurchaseCustomers,
CONTAINS ( VALUES ( {DateColumn} ), {DateColumn}, [DateOfFirstBuy] )
)
RETURN
CALCULATE (
{Base value},
NewCustomers
)
Example:
Sales New Customers =
VAR FirstPurchaseCustomers =
ADDCOLUMNS (
ALL ( Sales[CustomerKey] ),
"DateOfFirstBuy", CALCULATE ( MIN ( Sales[Order Date] ), ALLEXCEPT ( Sales, Sales[CustomerKey] ) )
)
VAR NewCustomers =
FILTER (
FirstPurchaseCustomers,
CONTAINS ( VALUES ( 'Date'[Date] ), 'Date'[Date], [DateOfFirstBuy] )
)
RETURN
CALCULATE ( [Sales Amount], NewCustomers )
eyJrIjoiODhiNTg0YzAtNmU5MC00YTBjLTlmODUtZmY5ZTJkYTE0MjcxIiwidCI6ImY1NDViZDY2LTdjM2YtNDcyOS04NTFhLWI3Y2EzYWM5ZmI2ZSIsImMiOjh9