Forum Discussion

VazhaPBI's avatar
VazhaPBI
Frequent Visitor
3 years ago
Solved

HELP - Calculated Column Sales Between Dates For Each Row

Hello World,   Need help to calculate Sales Amount between Start Date and End Date, for each agreementID independently, Table A and Table B are Related with clientID, when i try to calculate column...
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, VazhaPBI 

     

    Try this function. There is no relationship between the two tables.

    Sales Amount = 
CALCULATE (
    SUM ( 'Table B'[Sales Amount] ),
    FILTER (
        'Table B',
        [Date] >= EARLIER ( 'Table A'[Start Date] )
            && [Date] <= EARLIER ( 'Table A'[End Date] )
            &&[ClientID]=EARLIER('Table A'[ClientID])
    )
)

     

    Best Regards,

    Community Support Team _Charlotte

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

v-zhangti's avatar
v-zhangti
Community Support
3 years ago

Hi, VazhaPBI 

 

You can try the following methods.
Column:

Sales Amount =
CALCULATE (
    SUM ( 'Table B'[Sales Amount] ),
    FILTER (
        'Table B',
        [Date] >= EARLIER ( 'Table A'[Start Date] )
            && [Date] <= EARLIER ( 'Table A'[End Date] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

VazhaPBI's avatar
VazhaPBI
Frequent Visitor
3 years ago

Thank You, for reply but I still have same problem, uploading some more  screenshots. i cant understand what im doing wrong.

 

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, VazhaPBI 

     

    Note that no relationship is required between the two calculated tables.

     

    Best Regards,

    Community Support Team _Charlotte

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

     

    • VazhaPBI's avatar
      VazhaPBI
      Frequent Visitor
      3 years ago

      But how expression understands without relationship, which client ID to sum up, if I have several other client iD,s in data, if not relationship than i have to use CLientID = CLientID and I still get same result. I just need it to make sum for every row independently, but what it does is just sum on one row with earlier start date.

      • v-zhangti's avatar
        v-zhangti
        Community Support
        3 years ago

        Hi, VazhaPBI 

         

        Try this function. There is no relationship between the two tables.

        Sales Amount = 
CALCULATE (
    SUM ( 'Table B'[Sales Amount] ),
    FILTER (
        'Table B',
        [Date] >= EARLIER ( 'Table A'[Start Date] )
            && [Date] <= EARLIER ( 'Table A'[End Date] )
            &&[ClientID]=EARLIER('Table A'[ClientID])
    )
)

         

        Best Regards,

        Community Support Team _Charlotte

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