topic Re: HELP - Calculated Column Sales Between Dates For Each Row in DAX Commands and Tips

HELP - Calculated Column Sales Between Dates For Each Row

VazhaPBI — Tue, 29 Nov 2022 18:41:53 GMT

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.

Re: HELP - Calculated Column Sales Between Dates For Each Row

tamerj1 — Tue, 29 Nov 2022 19:32:22 GMT

Hi VazhaPBI

Please try

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

Re: HELP - Calculated Column Sales Between Dates For Each Row

v-zhangti — Wed, 30 Nov 2022 01:46:22 GMT

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.

Re: HELP - Calculated Column Sales Between Dates For Each Row

VazhaPBI — Wed, 30 Nov 2022 17:50:16 GMT

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

Re: HELP - Calculated Column Sales Between Dates For Each Row

VazhaPBI — Wed, 30 Nov 2022 17:53:20 GMT

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.

Re: HELP - Calculated Column Sales Between Dates For Each Row

v-zhangti — Thu, 01 Dec 2022 03:17:44 GMT

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.

Re: HELP - Calculated Column Sales Between Dates For Each Row

VazhaPBI — Thu, 01 Dec 2022 05:57:13 GMT

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.

Re: HELP - Calculated Column Sales Between Dates For Each Row

v-zhangti — Thu, 01 Dec 2022 09:17:28 GMT

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.