Problem with DAX Intersect Function

[SteveD2]

I've created to measures to count and total sales for new customers. The equations look like this.

Count of Returning Customer V2 = 
Var CustomersList = VALUES(Orders[CustomerID])
Var PriorCustomers = CALCULATETABLE(VALUES(Orders[CustomerID]),
    FILTER(ALL('Date Table'), 
        'Date Table'[Date] < MIN('Date Table'[Date])))
Return
COUNTROWS(
    INTERSECT(CustomersList, PriorCustomers))

And

Returning Customer Sales V2 = 
Var CustomersList = VALUES(Orders[CustomerID])
Var PriorCustomers = CALCULATETABLE(VALUES(Orders[CustomerID]),
    FILTER(ALL('Date Table'), 
    'Date Table'[Date] < MIN('Date Table'[Date])))
Return
CALCULATE([Total Sales],
    INTERSECT(CustomersList, PriorCustomers))

They appear identical but, when I put them in a table the Sales calculation includes new customers.

 Any suggestion on what I'm doing wrong?

[SteveD2]

G'day Greg,

My data source is the Northwinds database. I can attach the PBI file if that helps?

yes, your suggested alternative would work but, the next problem I have is I am trying dynamically categorise customers as New, Returning or Lost and will need a measure for each (?).

[Anonymous]

@SteveD2,

You can share PBIX file through online services such as OneDrive, also post expected result.

Regards,
Lydia

[Greg_Deckler]

No sample data so hard to troubleshoot. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, why not just change your 2nd measure to:

Returning Customer Sales V2 = 
IF(NOT(ISBLANK([Count of Returning Customer V2])),[Total Sales],BLANK())

[SteveD2]

Greg,

My Apologies for not replying sooner but, I got busy doing other things. I'm attaching a link to a PBIX file that highlights the issue using the Intersect function. The Column headings identify those calculations that are working correctly ( and use Intersect) and the final column which is not calculating as it should (and uses intersect). I'm sure its something to do with context transition but, I can't see how its changed for that one measure.

Intersect calculation issue

I hope this helps.