averagex + values + filter + all

[Anonymous]

Hello guys, I need help. Come on:

I have a stock table with the following values:

1/15/2020 qty 50

01/18/2020 qty 30

I need to calculate the average, so in logic we have:

1/15/2020 qte 50

1/16/2020 qte 50 (since there was no movement, the balance is the same as the previous day)

01/17/2020 qte 50 (as there was no movement so the balance is the same as the previous day)

01/18/2020 qte 30

Then resulting in: 50 + 50 + 50 + 30 = 180, I take the total and divide it by the days (in this case 4), ending 180/4 = 45.

But I can only get the result 40:

1/15/2020 qte 50

01/18/2020 qte 30

80/2=40.

Can you help me?

[Anonymous]

just for me to understand, you created in memory a table with all the dates between the minimum and the maximum date, then looked for value and at the end, if the value is zero (empty) you fill in the last value found, otherwise you get the own value and lastly you averaged with averagex considering this 'table' and the quantities, would this be the process done?

[Fowmy]

Yes, you are right.

I found your table has multiple opening balances for the same date which was not the case in your original sample.
You need to tell me what is the unique combination, In any visual, you should include that field as well

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Anonymous]

Yes, it can happen, to try to solve this, I created a 'Balance' measure that makes a sum (fato_estoque_gado [est_qtde]) to group the values of the day, but the lookupvalue function does not allow me to insert it in the first parameter.

[Anonymous]

please, sorry but when I applied the production model the same error occurred. Again a new file on the link, correct relationships, correct formula (I replaced the correct names) and nothing, can you see please?

https://drive.google.com/drive/folders/1dbdEHqxzPYeB-GeDSlT4LuoUdOvtB3ki?usp=sharing

[Anonymous]

you are the best, thank you very much!!!

[Fowmy]

@Anonymous 

Try this Measure: 

Avg Stock =
VAR CDATE =
    MAX ( Stock[Date] )
VAR LDATE =
    CALCULATE ( MAX ( Stock[Date] ), Stock[Date] < CDATE, ALL ( Stock[Date] ) )
VAR DAYSDIFF = CDATE - LDATE
VAR CQTY =
    SUM ( Stock[Opening] )
VAR LQTY =
    CALCULATE ( SUM ( Stock[Opening] ), Stock[Date] = LDATE )
RETURN
    ( LQTY * DAYSDIFF + CQTY ) / ( DAYSDIFF + 1 )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Anonymous]

It didn't work for the whole context, until the 18th it went well, but after the 18th and in this example until the 25th it should bring me 40 but it is 36.25. Whenever a day is not in my table I need to fill that day with the balance from the previous day.

Only on days in BLUE I have values, but implicitly I need the days in YELLOW to contain the stock of the previous day, IF they are empty ...

440/11=40

[amitchandak]

@Anonymous , Create a new column like this , this will let you now for how many days you have inventory

Inv day =
var _1 = datediff(Table[Date], minx(filter(Table,Table[Date] >earlier(Table[Date])) ,Table[Date]) -1,day)
return
if(isblank(_1),1,_1)

Then try a measure like

Measure = divide(sumx(Table,[qte]*[Inv Day]),sum(Table[Inv Day]))

[Anonymous]

Dont work firend, but thanks!