Distinct count of lines meeting certain criteria

[swisdom]

Link to raw data sample 
Link to sample PBI file 

I have a PBI that contains a bunch of RMA entries with various date fields. I am looking to build a measure which will give me a "Units In House" at the end of each month. ON the last day of the month, it should give a count of the lines in which the "Date Received" is BEFORE that date, and the "Actual Ship Date" Is either AFTER that date, or blank. (AKA the units which have been received, but not yet shipped, as of the last day of the month).

My DAX code:

Units In House = 
var dt = endofmonth('Date'[Date])
return
calculate( 
    calculate(distinctcount('Table1'[NCR_ID]), 
    'Table1'[Date Received]<=dt , or(isblank('Table1'[Date Actual Ship]), 'Table1'[Date Actual Ship]>dt ) ),
    crossfilter( 'Date'[Date],'Table1'[Date Received],None ) )

The crossfilter at the end is being used to break the model relationship between Date Received and Date (so that it isnt only giving those which were received in that month).

I wanted to have someone double check my code is actually doing what I want it to be doing, because I am getting higher numbers than I would expect (by about double). Thoughts?

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[swisdom]

I have, there are links at the top of my post.

[Icey · Accepted solution]

Hi @swisdom ,

Is this the result you expect?

Units In House 2 = 
VAR dt =
    ENDOFMONTH ( 'Date'[Date] )
RETURN
    CALCULATE (
        CALCULATE (
            DISTINCTCOUNT ( 'Table1'[NCR_ID] ),
            'Table1'[Date Received] <= dt,
            OR ( ISBLANK ( 'Table1'[Date Actual Ship] ), 'Table1'[Date Actual Ship] > dt )
        ))

Best Regards,

Icey

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