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
     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

Fowmy's avatar
Fowmy
Super User
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