calculate sum of quantity for each user_id on a particular date 12/12/2023

[numaan_99]

total_qty =

CALCULATE(

    SUMX(Stock,Stock[Items.quantity]),

    ALLEXCEPT(Stock, Stock[Created_At]),

    Stock[Created_At] = DATE(2023, 12, 12),

    Stock[user_id] IN {"LTTN01001","LTTN01002", "LTTN01003","LTTN01005","LTTN01006"}

)

but i am getting same total sum of quantity result for all the rows. please help me

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the Created_At date column to the Date column of the Calendar Table.  Write this measure

total_qty = CALCULATE(SUM(Stock[Items.quantity]),datesbetween(calendar[Date],DATE(2023, 12, 12),DATE(2023, 12, 12)),stock[user_id] IN {"LTTN01001","LTTN01002", "LTTN01003","LTTN01005","LTTN01006"})

Hope this helps.

[numaan_99]

sir. slight modification to my question above.

i have two tables "Sale_items", "stocks"

columns "term_id_new", "Sale Date" is from table "Sale_items"

column "items.quantity" is from table "stocks".

now i want to calculate the sum of "items.quantity" for each "term_id_new" for each "Sale Date".

please help me. am so new to this tool. please provide the query formula in detail.

[ryan_mayu]

maybe you can try this

CALCULATE(SUM(Stock[Items.quantity]),FILTER(ALL(Stock),

   Stock[Created_At] = DATE(2023, 12, 12)&& Stock[user_id]=MAX( Stock[user_id])&&

    Stock[user_id] IN {"LTTN01001","LTTN01002", "LTTN01003","LTTN01005","LTTN01006"}))

[numaan_99]

Bro. slight modification to my question above.

i have two tables "Sale_items", "stocks"

columns "term_id_new", "Sale Date" is from table "Sale_items"

column "items.quantity" is from table "stocks".

now i want to calculate the sum of "items.quantity" for each "term_id_new" for each "Sale Date".

please help me. am so new to this tool. please provide the query formula in detail.

[ryan_mayu]

In order to provide the proper solution. pls provide some sample data of two tables and the expected output