Forum Discussion

New Member

1 year ago

Solved

Error when calculating Returning Customers

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"
)

Need Help

Anonymous

1 year ago

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.

Error when calculating Returning Customers.pbix43 KB

speedramps

1 year ago

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

8 Replies

Anonymous

Not applicable

1 year ago

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.

Error when calculating Returning Customers.pbix43 KB

EllieSiroco1
New Member
1 year ago
thank you very much Anonymous this works for me

speedramps

Super User

1 year ago

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
New Member
1 year ago
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
  Super User
  1 year ago
  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

EllieSiroco1

New Member

1 year ago

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

Super User

1 year ago

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

Ashish_Mathur
Super User
1 year ago
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.

Forum Discussion

Error when calculating Returning Customers

8 Replies

Related content

How to make a DAX calculation return BLANK rows

Dynamic Market Return Calculations

If Statement Calculated Column Returning Incorrectly

Calculations with measures return odd results

DAX help calculating Repeat Customers