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 

I have created a different measure that works as per your expectation I hope. Please check the attached file.

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

 

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

 





Anonymous's avatar
Anonymous
Not applicable
6 years ago

 

when I use the field 'data' in the line this error appears:

without the 'data' field it works normally, what can it be?

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    Anonymous 

     

    Try in a Table visual where you can insert the dates and opening balances as o showed in my screenshot.

     

    Make sure you have a date column in your 

     Table and the opening balances. 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Dont work

       

      My table:

       

      • 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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      If I remove the relationship from the fact to the data dimension the error does not occur. But the relationship is correct ...