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...
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

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.

      • awitt's avatar
        awitt
        Helper III
        4 years ago

        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?