topic Re: sum over Stock by month in DAX Commands and Tips

sum over Stock by month

Sofinobi — Wed, 18 Jan 2023 10:05:08 GMT

Hello again community,

please, i come back for your help again to calculate the sum of over stock,

now, i have :
1 - the measure to calculate the average quantity in stock by month [Avg Qte]
2- and with the same measure i have the average price by month [Avg Price]
2 - the measure to calculate the average sales quantity of last 3 months [Avg Sales Qte]

please, i need to calcu late The over Stock by month with equal to = ([Avg Qte]-[Avg Sales Qte])*[Avg Price]
i tried this simple measure, but it doesn't keep in mind the different ITEMs

thank you all

Re: sum over Stock by month

ERD — Wed, 18 Jan 2023 10:19:04 GMT

Sofinobi , you can try something like

yourmeasure =
VAR current_item = SELECTEDVALUE ( data[item] )
RETURN
CALCULATE ( YOUR_EXPRESSION, data[item] = current_item )

Re: sum over Stock by month

Sofinobi — Wed, 18 Jan 2023 10:55:35 GMT

Hi, thank you for your answer,
but itsn't what i'm looking for: your measure calculate only when i filter by ITEM
me i need to calculate the global over stock and represent it in a line chart by month, also it reacts when i filter by ITEM

thank you

Re: sum over Stock by month

Sofinobi — Wed, 18 Jan 2023 11:43:49 GMT

i'll try to explain more,
what the over stock that i need is something like SUMX (line by line)
eg : ([Avg Qte]item1-[Avg Sales Qte]item1)*[Avg Price]item1 + ([Avg Qte]item2-[Avg Sales Qte]item2)*[Avg Price]item2

Re: sum over Stock by month

Sofinobi — Wed, 18 Jan 2023 12:16:58 GMT

msg 3:
i have a sollution in my mind, that i create a summarized table (from 2 tables : date table and item table)
after that, i'll calculate the sum of over stock using SUMX
but i don't know how to create summarize from 2 tables

thank you

Re: sum over Stock by month

ERD — Wed, 18 Jan 2023 15:33:04 GMT

Please, provide some sample data (not an image) and the result you want to achieve (how it looks like calculated).

Re: sum over Stock by month

Sofinobi — Tue, 24 Jan 2023 10:59:05 GMT

Hi ERD sorry to be late for the answer
here is the sample of my data in pbix file
actualy, the over stock value is correct only if i filter by product, if i turn off the filter the result is wrong (because the averages that i have dont take consideration of every product separately)
please if you can help.
thank you.
OverStock.pbix