Forum Discussion

awitt's avatar
awitt
Icon for Helper III rankHelper 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
Icon for Super User rankSuper 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
Icon for Helper III rankHelper 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?