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.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi VazhaPBI 

Please try

Sales Amount =
SUMX (
FILTER (
RELATEDTABLE ( TableB ),
TableB[Date] >= TableA[Start Date]
&& TableB[Date] <= TableA[End Date]
),
TableB[Sales Amount]
)

  • VazhaPBI's avatar
    VazhaPBI
    Frequent Visitor
    3 years ago

    Thank you tamerj1 but i still have same problem, cant figure out why 😞 I uploaded more screenshots in reply above, both solutions posted give me same result. Sales Amount for only one row not both rows.