Error when calculating Returning Customers

[EllieSiroco1]

Ihave a PowerBi table called "orders-web2019" with the following dimensions: "Order date", "customer email" , and the following metric "Sales inc VAT".

I have written DAX to define a new calculated column with the name "returning customers" that shows customers (based on the "customer email" dimension) who generated sales (based on 'orders-web2019'[Sales inc VAT] > 0) in the period between "01 November 2023 to 31 December 2023" (order date) and returned to generate sales again in the period between the order dates "01 November 2024 to 31 December 2024". The rest of the customers would be identified as "New".

I have encountered the following error "A function "placeholder" has been used in a True/False Expression as a table that is used as filter expression , This is not allowed"

for this snippet of DAX  - where is the mistake? Thank you for your help in advance

Returning Customers =
   VAR CurrentCustomerEmail = 'orders-web2019'[customer email]
   VAR CurrentOrderDate = 'orders-web2019'[Order date]
    VAR PreviousPeriodStart = DATE ( 2023, 11, 01 )
VAR PreviousPeriodEnd = DATE ( 2023, 12, 31 )
VAR CurrentPeriodStart = DATE ( 2024, 11, 01 )
VAR CurrentPeriodEnd = DATE ( 2024, 12, 31 )


VAR CustomerInPreviousPeriod =
CALCULATE (
COUNTROWS ( 'orders-web2019' ),
'orders-web2019'[customer email] = CurrentCustomerEmail,
'orders-web2019'[Order date] >= PreviousPeriodStart,
'orders-web2019'[Order date] <= PreviousPeriodEnd,
'orders-web2019'[Sales inc VAT] > 0
)


VAR CustomerInCurrentPeriod =
CALCULATE (
COUNTROWS ( 'orders-web2019' ),
'orders-web2019'[customer email] = CurrentCustomerEmail,
'orders-web2019'[Order date] >= CurrentPeriodStart,
'orders-web2019'[Order date] <= CurrentPeriodEnd,
'orders-web2019'[Sales inc VAT] > 0
)


RETURN
IF (
CustomerInPreviousPeriod > 0
&& CustomerInCurrentPeriod > 0,
"Returning",
"New"
)

[Anonymous · Accepted solution]

Hi @EllieSiroco1 

Please try this:

Here I create a set of sample:

Then add a calculated column:

Returning Customers =
VAR CurrentPeriodStart =
    DATE ( 2024, 11, 01 )
VAR CurrentPeriodEnd =
    DATE ( 2024, 12, 31 )
VAR _previousSales2 =
    CALCULATE (
        SUM ( 'orders-web2019'[Sales inc VAT] ),
        FILTER (
            ALLSELECTED ( 'orders-web2019' ),
            'orders-web2019'[customer email] = EARLIER ( 'orders-web2019'[customer email] )
                && YEAR ( 'orders-web2019'[Order date] )
                    = YEAR ( EARLIER ( 'orders-web2019'[Order date] ) ) - 1
                && MONTH ( 'orders-web2019'[Order date] )
                    = MONTH ( EARLIER ( 'orders-web2019'[Order date] ) )
        )
    )
RETURN
    IF (
        'orders-web2019'[Order date] >= CurrentPeriodStart
            && 'orders-web2019'[Order date] <= CurrentPeriodEnd,
        IF (
            _previousSales2 > 0
                && 'orders-web2019'[Sales inc VAT] > 0,
            "Returning",
            "New"
        )
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @EllieSiroco1 

Please try this:

Here I create a set of sample:

Then add a calculated column:

Returning Customers =
VAR CurrentPeriodStart =
    DATE ( 2024, 11, 01 )
VAR CurrentPeriodEnd =
    DATE ( 2024, 12, 31 )
VAR _previousSales2 =
    CALCULATE (
        SUM ( 'orders-web2019'[Sales inc VAT] ),
        FILTER (
            ALLSELECTED ( 'orders-web2019' ),
            'orders-web2019'[customer email] = EARLIER ( 'orders-web2019'[customer email] )
                && YEAR ( 'orders-web2019'[Order date] )
                    = YEAR ( EARLIER ( 'orders-web2019'[Order date] ) ) - 1
                && MONTH ( 'orders-web2019'[Order date] )
                    = MONTH ( EARLIER ( 'orders-web2019'[Order date] ) )
        )
    )
RETURN
    IF (
        'orders-web2019'[Order date] >= CurrentPeriodStart
            && 'orders-web2019'[Order date] <= CurrentPeriodEnd,
        IF (
            _previousSales2 > 0
                && 'orders-web2019'[Sales inc VAT] > 0,
            "Returning",
            "New"
        )
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EllieSiroco1]

thank you very much @Anonymous this works for me

[Ashish_Mathur]

Hi,

Share some data, explain the questionn and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[EllieSiroco1]

@speedramps thank you for the solution, I am not entirely sure how to adapt it , as i do not have a "minimum date". I am comparing two very specific periods:
between "01 November 2023 to 31 December 2023" (order date)  and the period between the order dates "01 November 2024 to 31 December 2024, rather than the period before a date in the entire calendar

[speedramps · Accepted solution]

@EllieSiroco1 

Sorry I think I misunderstood you before.

I think this simply solution will do what you you want ...

Create 4 measures ... 

Has NovDec2023 sales = 
// create a temp file of dates in range 
VAR mydates =  FILTER('Calendar', 'Calendar'[Date] >= DATE(2023,11,01) && 'Calendar'[Date] <= DATE(2023,12,31))
RETURN
// return 1 if there were any sales within date range
CALCULATE(
INT(NOT(ISEMPTY(Sales))),
mydates
)

Has NovDec2024 sales = 
// create a temp file of dates in range 
VAR mydates =  FILTER('Calendar', 'Calendar'[Date] >= DATE(2024,11,01) && 'Calendar'[Date] <= DATE(2024,12,31))
RETURN
// return 1 if there were any sales within date range
CALCULATE(
INT(NOT(ISEMPTY(Sales))),
mydates
)

New customers = 
// create temp file of qualifiying customers  
var mysubset =
FILTER(VALUES(Sales[CustomerKey]), 
    [Has NovDec2023 sales] = 0 &&
    [Has NovDec2024 sales] = 1)
 RETURN
 // count the rows
 COUNTROWS(mysubset)  

Returning customer = 
// create temp file of qualifiying customers  
var mysubset =
FILTER(VALUES(Sales[CustomerKey]), 
    [Has NovDec2023 sales] = 1 &&
    [Has NovDec2024 sales] = 1)
 RETURN
 // count the rows
 COUNTROWS(mysubset)   

Please click the [accept solution] and thumbs up button. Thank you.

Click here to download PBIX example from Onedrive

Click here 

[speedramps]

Try these adapt these DAX measure instead of calculated columns.
DAX measures are much better than calculated columns because the user can pick and chose the duration

NO new customers = 
/* DOCUMENTATION
Get number of new customers as follows:-
Use addcolumns to get a set with (ResellerKey,Previous Rows)
Then filters the set to just to include customers Keys with no previous rows.
Then count the number of customers 
*/

VAR mindate =  MIN ( 'Calendar'[Date] ) 

VAR NewCustomers  = 
    FILTER (
        ADDCOLUMNS (
            VALUES ( Sales[CustomerKey] ),
            "PreviousRows", 
                CALCULATE (COUNTROWS (Sales),
                FILTER (ALL ( 'Calendar'[Date] ),'Calendar'[Date] < mindate))
        ),
        [PreviousRows] = 0
    )

RETURN
COUNTROWS(NewCustomers) 

NO returning customers = 
/* DOCUMENTATION *
Number of returning customers
*/
VAR mindate = MIN('Calendar'[Date])

RETURN
COUNTROWS (
    CALCULATETABLE (
        VALUES ( Sales[CustomerKey] ),
        VALUES ( Sales[CustomerKey] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] < mindate 
        )
    )
)

I would be most grateful if you click the [accept solution] and thumbs up buttons.

Thank you 

[EllieSiroco1]

@speedramps this is a different model (table) structure for me, and it uses measures instead of a calculated column, but i can see the logic behind it, and I have applied it with changes to fit my model. This is a different , alternative approach - thank you

[speedramps]

Novices typicaly start out by using calculated columns (because they are like Excel).
We all star that way (I recall I did) but then we learn that measures are so much better and dynamic.

Please click [accept solution] and the thumbs up button for mine and other helpers solutions.

Thank you