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 

 

Hope you replaced the table and fields correctly?  Check my attached PBIX file. 

You may share your file so I can have a look at it. 

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    Anonymous 

    The file is protected, I sent the request, please approve or share without the password.

    Thanks

      • Fowmy's avatar
        Fowmy
        Super User
        6 years ago

        Anonymous 

        I have modified the formula to include the calendar table:
        The table should have the Date from the calendar

        https://1drv.ms/u/s!AmoScH5srsIYgYIV5Fv7sACs6-7WtQ?e=ejEmAI

        Average Stock Date = 
VAR _CURRENTDATE = 
    SELECTEDVALUE(
        'Calendar'[Date],
        CALCULATE(
            MAX(Stock[Date]),
            ALLSELECTED('Calendar'[Date])           )
    )
VAR _STARTDATE = 
    CALCULATE(
        MIN('Calendar'[Date]),
        ALLSELECTED('Calendar'[Date]))
VAR _DATES = 
    CALCULATETABLE(
        GENERATESERIES( _STARTDATE, _CURRENTDATE,1),
        ALLSELECTED('Calendar'[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('Calendar'))
        VAR _LASTQTY = CALCULATE(SUM (Stock[Opening]),'Calendar'[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 🙂

        YouTube, LinkedIn