Cumulative Count based on multiple criteria

TD21 · ‎10-03-2019

Need assitance calculating cumulative count with multiple filtering criteria to be used in a chart.

I'm working with 2 tables:

Table #1: DATE_MASTER (it's a date table)

Table #2: SALES_EXPORT (contains the following fields)

ORDER_DATE
CUSTOMER_NAME
GENDER
CHANNEL (i.e. Web, Mobile App, Store)
SHIPPING_METHOD (i.e. Store pick-up, UPS, Fedex)

Below is my measure to calculate cumulative count:

SALES_CUMULATIVE_COUNT:=
IF (
MIN ( DATE_MASTER[Date] ) <= CALCULATE ( MAX ( [ORDER_DATE] ), ALL ( SALES_EXPORT ) ),
CALCULATE (
COUNTX ( SALES_EXPORT, [ORDER_DATE] ),
USERELATIONSHIP ( DATE_MASTER[Date], SALES_EXPORT[ORDER_DATE] ),
FILTER (
DATE_MASTER,
DATE_MASTER[Date] >= DATE ( 2019, 1, 1 ) && DATE_MASTER[Date] <= MAX ( DATE_MASTER[Date] )
)
)
)

I would like to calculative the cumulative count of sales by Female, using Mobile App, but picked up in the store. How do I tweak my measure to include GENDER = 'F' && CHANNEL = 'Mobile App' && 'SHIPPING_METHOD' = 'Store pick-up'?

Thanks in advance.

v-yuta-msft · ‎10-04-2019

@TD21 ,

You may modify the measure like below:

SALES_CUMULATIVE_COUNT :=
IF (
    MIN ( DATE_MASTER[Date] )
        <= CALCULATE ( MAX ( [ORDER_DATE] ), ALL ( SALES_EXPORT ) ),
    CALCULATE (
        COUNTX ( SALES_EXPORT, [ORDER_DATE] ),
        USERELATIONSHIP ( DATE_MASTER[Date], SALES_EXPORT[ORDER_DATE] ),
        FILTER (
            DATE_MASTER,
            DATE_MASTER[Date] >= DATE ( 2019, 1, 1 )
                && DATE_MASTER[Date] <= MAX ( DATE_MASTER[Date] )
                && DATE_MASTER[GENDER] = "F"
                && DATE_MASTER[CHANNEL] = "Mobile App"
                && DATE_MASTER[SHIPPING_METHOD] = "Store pick-up"
        )
    )
)

Community Support Team _ Jimmy Tao

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