averagex + values + filter + all

[Anonymous]

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

1/16/2020 qte 50 (since there was no movement, the balance is the same as the previous day)

01/17/2020 qte 50 (as there was no movement so the balance is the same as the previous day)

01/18/2020 qte 30

Then resulting in: 50 + 50 + 50 + 30 = 180, I take the total and divide it by the days (in this case 4), ending 180/4 = 45.

But I can only get the result 40:

1/15/2020 qte 50

01/18/2020 qte 30

80/2=40.

Can you help me?

[Fowmy]

@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]

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

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

[Fowmy]

@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]

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

[Anonymous]

Dont work

My table:

[Fowmy]

@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]

https://drive.google.com/file/d/10dheND1LmmE54Y6wJf_AFS9_Sf0g3uyY/view?usp=sharing,%20https://drive....

[Fowmy]

@Anonymous 

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

Thanks

[Anonymous]

Sorry, try again

https://drive.google.com/drive/folders/1dbdEHqxzPYeB-GeDSlT4LuoUdOvtB3ki?usp=sharing

But your example is not entirely correct for what I need, see:

[Fowmy]

@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

[Anonymous]

I don't know what I'm doing wrong ... See my file again, I'm attaching it again. I created a calendar table and made the list. Replaces all the fields in your formula with the fields in my example and it didn't work ...

https://drive.google.com/drive/folders/1dbdEHqxzPYeB-GeDSlT4LuoUdOvtB3ki?usp=sharing

[Fowmy]

@Anonymous 

Your model is too slow. Remove the extra calendar table which has dates from 1900,

make the relation ship between fact table and dates table as one-to-many

[Anonymous]

I already changed the cardinality, they are the same as your example, but again it doesn't work.

[Fowmy]

@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

[Anonymous]

Please review one last time, the values are not matching. In this example I'm sending, my FACT table has sk_data that references the DIM_DATA dimension. The averages didn't match ...

https://drive.google.com/drive/folders/1dbdEHqxzPYeB-GeDSlT4LuoUdOvtB3ki?usp=sharing

[Fowmy]

@Anonymous 

The relationship is changed to date index field but you can still use the same measure I shared and no need to modify anything there in the measure.

Check the file: https://1drv.ms/u/s!AmoScH5srsIYgYIWIHihT5prYIyz8w?e=54ykCq

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Anonymous]

Delete the files you have and use those from the link, see that I no longer have the date on the FACT but the reference on the DIM_DATA.

https://drive.google.com/drive/folders/1dbdEHqxzPYeB-GeDSlT4LuoUdOvtB3ki?usp=sharing

[Fowmy]

@Anonymous 

When you remove a field that was part of my solution, you cannot expect the measures to work as you expect.

Thanks

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn
 

[Anonymous]

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 · Accepted solution]

@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