Forum Discussion
averagex + values + filter + all
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
I have modified only the measure to replace the date field. I used the same file use sent me last,
Check the file: https://1drv.ms/u/s!AmoScH5srsIYgYIbFhfhthmc42ggVw?e=1jQjGfAverage Stock = VAR _CURRENTDATE = MAX(dim_data[sk_data]) VAR _STARTDATE = MIN(dim_data[sk_data]) VAR _DATES = GENERATESERIES( _STARTDATE, _CURRENTDATE,1) VAR T1 = ADDCOLUMNS( _DATES, "QTY", VAR _CURRENTQTY = lOOKUPVALUE(f_estoquegado[qtde],f_estoquegado[sk_data],[Value]) VAR _DATEQTY = CALCULATE(MAX(f_estoquegado[sk_data]),dim_data[sk_data]<= EARLIER([Value]),ALL(f_estoquegado)) VAR _LASTQTY = CALCULATE(SUM (f_estoquegado[qtde]),dim_data[sk_data] = _DATEQTY,ALL(f_estoquegado)) RETURN IF( ISBLANK(_CURRENTQTY), _LASTQTY, _CURRENTQTY ) ) RETURN AVERAGEX( T1, [QTY] )________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
29 Replies
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
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 🙂
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
Anonymous
I have created a different measure that works as per your expectation I hope. Please check the attached file.
https://1drv.ms/u/s!AmoScH5srsIYgYIV5Fv7sACs6-7WtQ?e=8PkZ0YAverage Stock = VAR _CURRENTDATE = SELECTEDVALUE(Stock[Date],CALCULATE(MAX(Stock[Date]),ALLSELECTED(Stock[Date]))) VAR _STARTDATE = CALCULATE(MIN(Stock[Date]),ALLSELECTED(Stock[Date])) VAR _DATES = CALCULATETABLE( GENERATESERIES( _STARTDATE, _CURRENTDATE,1), ALLSELECTED(Stock[Date]) ) VAR T1 = ADDCOLUMNS( _DATES, "QTY", VAR _CURRENTQTY =LOOKUPVALUE(Stock[Opening],Stock[Date],[Value]) VAR _DATEQTY = CALCULATE(MAX(Stock[Date]),Stock[Date]< EARLIER([Value]),ALL(Stock)) VAR _LASTQTY = CALCULATE(SUM (Stock[Opening]),Stock[Date] = _DATEQTY) RETURN IF( ISBLANK(_CURRENTQTY), _LASTQTY, _CURRENTQTY ) ) RETURN AVERAGEX( T1, [QTY] )________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
Anonymous
Try in a Table visual where you can insert the dates and opening balances as o showed in my screenshot.
Make sure you have a date column in your
Table and the opening balances.
