Sum Value of specified product base of date filter of slicer

[DominicDao]

Dear All,

Please help me on this.

I had 02 tables: 1 is Product Table (unique value of Product Name) and 1 is Sales Table (with Date of Sales, Product Name (repeated by Dates of Sales) and Quantity of Sales.

I want to create a measure that when i filter Date Slicer in Bi report, Product Tables will show Sum of Qty for each Product within the dates that I selected.

Example as below:

Sales table
Dates of Sales	Product	Qty
8/1/2023	A	10
8/2/2023	B	5
8/3/2023	C	2
8/4/2023	A	2
8/2/2023	D	1
7/1/2023	B	4
7/5/2023	C	2
8/6/2023	A	3

Result should be as below:

	Start	End
Slicer Filter	7/5/2023	8/3/2023	-> this is slicer from report
Product Table
Product	Qty
A	10
B	5
C	4
D	1

Appreciated your help so much.

Dominic

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but one of ways to solve this is to create a datamodel that has good relationships, something like below.

[DominicDao]

Thanks for your ideas. Please allow me to explain clearer, i dont want the result to show in report, just want to create a measure in column Qty of Table Product, get the result of Table Sales base on filter of Date Slicer in report.

Warm Regards,

[tamerj1]

Hi @DominicDao 
Calculated Columns and calculated tables do not interact with the filter context. A calculated column is evaluated only once and won't change with slicer selection. This has to be a measure in the report. It seems to me that you need to use the quantity to do some sort of dynamic segmentation? If this is true please provide more details of what exactly are you trying to accomplish.

[DominicDao]

Hi @tamerj1 ,

With above intention, I planned to get value of products and use formula to assign that if value = 0, count as 0 and value >0, count as 1. Finally, the card will be measure that Sum of column that contain assignment.

Sorry, i am new to BI and learn myself so it will be very manually calculation.

Best Regards,

[tamerj1 · Accepted solution]

@DominicDao 
You may try this measure

Count =
SUMX (
    VALUES ( 'Product'[Product] ),
    INT ( CALCULATE ( SUM ( Sales[Qty] ) ) > 0 )
)

[DominicDao]

Hi tamerj1,

Thanks so much. It worked for me.

Warm Regards,

Dom