Forum Discussion
averagex + values + filter + all
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 🙂
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?
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 🙂
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.