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 

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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