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 

 

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.