Forum Discussion

awitt's avatar
awitt
Helper III
4 years ago
Solved

SUMX / Date Range Question

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. 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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's avatar
      awitt
      Helper III
      4 years ago

      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's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi, 

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

        The relationship has been deleted.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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