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 it sums amount only on 1st client ID, I need amount for each row independently even if there are same client ID's. 

 

 

 

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

7 Replies

  • tamerj1's avatar
    tamerj1
    Community 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.

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