SUMX / Date Range Question

[awitt]

I have a question I am trying to answer with multiple variables that is causing some challenges. 

I am wanting to sum the total orders only within a certain time frame for multiple customers. The challenge is that the date range is different per customer with frequet overlapping. For insance Customer 1 might have a date range I wish to evaluate of Jan 3 - Jan 17 and Customer 2 might have a date range of Jan 10 - Jan 24. These dates ranges per customer are identified in a separate table.

What is happening currently is that both customer's orders are being evaluated over the entire date range, Jan 3 - 24 for example, and not summing only the date range I wish to evaluate per customer.  The below image is how I would want the result to calculate. In reality there are thousands of customers and not just 2. 

[Anonymous · Accepted solution]

Hi @awitt ,

Create a measure like below.

measure =
CALCULATE (
SUM ( 'table1'[sales] ),
FILTER (
ALLSELECTED ( 'table1' ),
'table1'[customer] = SELECTEDVALUE ( 'table2'[customer] )
&& 'table1'[date] >= SELECTEDVALUE ( 'table2'[start] )
&& 'table1'[date] <= SELECTEDVALUE ( 'table2'[end] )
)
)

Use 'table2'[customer], table2[start], 'table2'[end] and [measure] to create a visual.

Best Regards,

Jay

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Expected result: =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        Sales,
        Sales[Date] >= RELATED ( Customer[Start Date] )
            && Sales[Date] <= RELATED ( Customer[End Date] )
    )
)

[awitt]

The only thing preventing this to work, and it's my fault for not mentioning it, is that I cannot have a many to one relationship with the two tables and so the Related function will not work.

The customer table might have several different records per customer with different date ranges over the course of multiple years. 

[Jihwan_Kim]

Hi, 

I am not sure if I understood your feedback correctly, but please check the attached file.

The relationship has been deleted.

[awitt]

So this measure does work, however when evaluating it in a table there seems to be some strangeness going on with the start date and filters. This table on the right has the data with no start date filter, and has many customers - in my case these customers are schools - filtered out. 

When I include a date filter for the start date like on the visual on the right, which is on the bottom of this message, everything gets included. Any idea why adding a filter would increase the amount of data being seen?