Count of customers by delivery status

[SJC]

Hello,

I have data in the table below and want to return a count of customers waiting for their second delivery and to be able to sumarise that number as a distinct count by a selected time period (in this case by month) - the number of waiters in the period.

The DAX I'm using works fine if the customer has a first delivery date but includes the customer in all months if their first date is blank/null.

The Dates table is simple but includes Date values for the date range in the Order table.

Any suggestions greatly appreciated.

Stephen

						Waiters For Second Delivery
Customer_ID	Order_ID	Order Date	First_Delivery_Date	Second_Delivery_Date		Jan-22	Feb-22	Mar-22
A	A01	01-Jan-22	09-Jan-22	04-Feb-22		1	1
B	BO1	05-Jan-22
A	AO2	10-Jan-22	02-Feb-22				1	1
C	CO1	20-Jan-22	22-Jan-22	27-Jan-22		1
B	BO2	25-Jan-22	08-Feb-22	17-Feb-22			1
						2	2	1

Waiters For Second Delivery:=
var MinDate = Min('Dates'[Date])
var MaxDate = Max('Dates'[Date])
var Result=
calculate (
DISTINCTCOUNT(Orders[Customer_ID]),
Orders[First_Delivery_Date] <=MaxDate, or(Orders[Second_Delivery_Date] >=MinDate , isblank(Orders[Second_Delivery_Date]))
)
return Result

[v-rongtiep-msft · Accepted solution]

Hi @SJC ,

Please refer to my pbix file to see if it helps you.

Create  measure.

Waiters For Second Delivery =
VAR MinDate =
    MIN ( 'Dates'[Date] )
VAR MaxDate =
    MAX ( 'Dates'[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Orders[Customer_ID] ),
        FILTER (
            ALL ( Orders ),
            Orders[First_Delivery_Date] <= MaxDate
                || ( Orders[Second_Delivery_Date] >= MinDate )
                    && ( Orders[Second_Delivery_Date] ) <> BLANK ()
        )
    )
RETURN
    Result

If I have misunderstood your meaning, please  explain to me the output you want.

Best Regards

Community Support Team _ Polly

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

[SJC]

Many thanks:  the inclusion of the FILTER function fixed the problem.

[amitchandak]

@SJC ,

Try like, You might have add not( isblank(Orders[First_Delivery_Date])) )

Waiters For Second Delivery:=
var MinDate = Min('Dates'[Date])
var MaxDate = Max('Dates'[Date])
var Result=
calculate (
DISTINCTCOUNT(Orders[Customer_ID]),
filter( (Orders[First_Delivery_Date] <=MaxDate || Orders[Second_Delivery_Date] >=MinDate) && ( isblank(Orders[Second_Delivery_Date])) && not( isblank(Orders[First_Delivery_Date])) )
)

or

Waiters For Second Delivery:=
var MinDate = Min('Dates'[Date])
var MaxDate = Max('Dates'[Date])
var Result=
calculate (
DISTINCTCOUNT(Orders[Customer_ID]),
filter( (Orders[First_Delivery_Date] <=MaxDate || Orders[Second_Delivery_Date] >=MinDate) && not( isblank(Orders[Second_Delivery_Date])) )
)

[SJC]

Many thanks:  your answer was very helpful - it did need a tweek but is working as expected.