Forum Discussion
Error when calculating Returning Customers
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.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
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
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
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
