Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    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=1jQjGf

    Average 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 🙂

    YouTube, LinkedIn
     

Fowmy's avatar
Fowmy
Super User
6 years ago

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