averagex + values + filter + all

Anonymous 

Please the file you submitted, I adjusted the relationship between the date fields and modified the measure.

https://1drv.ms/u/s!AmoScH5srsIYgYIWIHihT5prYIyz8w?e=MEwXqH

Simplified Measure:

Average Stock Date = 
VAR _CURRENTDATE = MAX(dim_data[data_completa])
VAR _STARTDATE = MIN(dim_data[data_completa])
VAR _DATES = GENERATESERIES( _STARTDATE, _CURRENTDATE,1)
VAR T1 = 
    ADDCOLUMNS(
        _DATES,
        "QTY",
        VAR _CURRENTQTY = lOOKUPVALUE(f_estoquegado[est_qtde],f_estoquegado[sk_data],[Value])
        VAR _DATEQTY =  CALCULATE(MAX(f_estoquegado[data]),dim_data[data_completa]<= EARLIER([Value]),ALL(f_estoquegado))
        VAR _LASTQTY = CALCULATE(SUM (f_estoquegado[est_qtde]),dim_data[data_completa] = _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 🙂

YouTube, LinkedIn

Please review one last time, the values are not matching. In this example I'm sending, my FACT table has sk_data that references the DIM_DATA dimension. The averages didn't match ...

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

Anonymous 

The relationship is changed to date index field but you can still use the same measure I shared and no need to modify anything there in the measure.

Check the file: https://1drv.ms/u/s!AmoScH5srsIYgYIWIHihT5prYIyz8w?e=54ykCq

________________________

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

I accept KUDOS 🙂

YouTube, LinkedIn

Delete the files you have and use those from the link, see that I no longer have the date on the FACT but the reference on the DIM_DATA.

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

Anonymous 

When you remove a field that was part of my solution, you cannot expect the measures to work as you expect.

Thanks

________________________

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

I accept KUDOS 🙂

YouTube, LinkedIn
 

In my model, I don't have (and shouldn't have) the date in FATO, but only the sk_data that makes reference to the dimension dim_data. I need the measure considering this, I know that as I am going to change the fields there, the values will be different, but I cannot solve this alone, please if you can help me again. And thanks in advance for your help and patience.

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

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

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 🙂

YouTube, LinkedIn

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.