Why is my measure not evaluating to 1?

[VBAmazing]

Hello Forum,

I have a series of Measures I am trying to use to identify the first instance of a Customer placing an Order. 

I need to accomplish three things:

1. Identify the Minimum Order Date across a series of dimensions
2. Return "1" for rows where the order falls within the date context
3. Sum all rows where "1" was returned

I am able to accomplish one and two, but my third Measure is giving unexpected results. Why are the highlighted values appearing as blank?

• 

__NewAccounts_A_FirstOrderDate:

__NewAccounts_A_FirstOrderDate = 

var maxContextDate      = MAX( Dates[Date] )

RETURN
CALCULATE(
    MIN( Sales[Delivery Date] ),
    ALLEXCEPT( 
        'Sales' , 
        Customer[Customer No.], 
        --Customer[Customer], 
        Supplier[Supplier], 
        Location[Branch]
        ),
    Sales[DeliveryDate] >= DATE( YEAR( maxContextDate ) - 1, 1, 1),
    Sales[Delivery Quantity] > 0
)

__NewAccounts_B_FirstOrderInContext:

__NewAccounts_B_FirstOrderInContext = 
var maxContextDate = MAX( Dates[Date] )

return
    IF(
        [__NewAccounts_A_FirstOrderDate] >= DATE( YEAR( maxContextDate ), 1, 1) && [__NewAccounts_A_FirstOrderDate] <= maxContextDate,
        1,
        0
    )

__NewAccounts_C_DistinctNewCustomers:

__NewAccounts_C_DistinctNewCustomers = 
 CALCULATE(
    DISTINCTCOUNT( Customer[Customer No.] ),
    FILTER(
        Sales,
        [__NewAccounts_B_FirstOrderInContext] = 1
    )
 )

[Anonymous]

Hi ALL,
Firstly  Dangar332 thank you for your solution!
And @VBAmazing ,I tried to use your code to run it in the example data I created but he seems to be running fine, since you can't show your example data, can you reproduce your problem based on the pbix file I provided.

You can contact me anytime and I will get back to you as soon as I receive your message, looking forward to hearing from you!

[Dangar332]

Hi, @VBAmazing 

Can you provide some sample Data and clarify your scenario in detail.

[VBAmazing]

@Dangar332 Thank you for your reply. The data is sensitive and unfortunately cannot be shared in a .PBIX, though that would be ideal.

However the scenario is:

• I need to identify Customers who placed their first order within one year of the Maximum Context Date
• I have a measure that correctly identifies the minimum Order Date by Customer (and a few other dimensions)
• I have another measure that identifies whether that minimum date falls in the desired date context

Now I need to be able to Count the Distinct Customer IDs where the minimum date falls in the context. 

Currently - as in my screenshot above - You can see those measures in action.

However, I am only able to count the distinct Customer IDs in some of my rows. 

Based on my current understanding of the logic, I should be getting a 1 in each __NewAccounts_C_DistinctNewCustomers row where __NewAccounts_B_FirstOrderInContext is 1, but that's not the case.