Forum Discussion
Helper IIIcalculate the average last inputs
Hello,
First, thank you for your help. I’m sorry for my insistence, but I need to be able to have this metric.
I have a PBI with a metric for calculate inputs.
Now I need a metric to calculate the average of the 7 last inputs .
For example, screenshot, for the day 02/02/2023 the an rounded to the nearest whole number average of the last 7 days is 55. Please note 28/02/2023 and 29/02/2023 there are no inputs.
Many thanks for your answers
MTrullàs use this then
Measure2 = VAR curr = MAX ( DimDate[Date] ) VAR top7AtCurr = MAXX ( FILTER ( ALL ( DimDate ), DimDate[Date] < curr - 5 ), DimDate[Date] ) VAR numerator = CALCULATE ( COUNT ( STOCK_EVOLUTION_1[Numero Bastidor] ), USERELATIONSHIP ( STOCK_EVOLUTION_1[Fecha_entrada_calculada], 'DimDate'[Date] ), FILTER ( ALL ( DimDate ), DimDate[Date] >= top7AtCurr && DimDate[Date] <= curr ) ) VAR denominator = COUNTROWS ( FILTER ( ALL ( STOCK_EVOLUTION_1[Fecha_entrada_calculada] ), STOCK_EVOLUTION_1[Fecha_entrada_calculada] >= top7AtCurr && STOCK_EVOLUTION_1[Fecha_entrada_calculada] <= curr ) ) RETURN DIVIDE ( numerator, denominator )They are identical
7 Replies
Hi MTrullàs ,
It appears that the data is not in ascending date order.
So the first step is to consider adding a new [Index] column to the fact table:
Then please try this measure:
Measure = VAR _index_max = MAX('Table'[Index]) VAR _index_min = _index_max-6 VAR _result = AVERAGEX(FILTER(ALLSELECTED('Table'),'Table'[Index]>=_index_min&&'Table'[Index]<=_index_max),[ENTRADAS]) RETURN _resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
MTrullàsHello @v-cgao-msftv-cgao-msft
Thank you very much for your answer.
It's a fantastic solution, but I have the problem that I don't have the index column in my table. It's my fault, because I didn't give the exact details.
Now, I attach the pbix, example to try to be more precise.
Thanks in advance for your time!
https://drive.google.com/drive/folders/1nsn6WXmAY0CYVYwuxCG0AkfoMxGNyitk?usp=sharing
- smpa01
Community Champion
MTrullàs does the following work?
Measure = AVERAGEX ( WINDOW ( -6, REL, 0, REL, ALLSELECTED ( DimDate[Date] ), ORDERBY ( DimDate[Date], ASC ) ), CALCULATE ( COUNT ( STOCK_EVOLUTION_1[Numero Bastidor] ), USERELATIONSHIP ( STOCK_EVOLUTION_1[Fecha_entrada_calculada], 'DimDate'[Date] ) ) )
- MTrullàs
Hi smpa01,
Thank you very much, the solution works perfectly!
Now, when I've tasted the solution, I've seen a new requirement.
I don't know, if I have to open a new post or I use the same. What is the best way for the forum?
In the meantime, I use the same post.
I would like a new measure in the same way, but I need to exclude Saturdays and Sundays. I need the average of "ENTRADAS" of the last 7 inputs but exluded the values of Saturdays and Sundays. I try to explain through the Excel:
Date ENTRADAS Measure2 DayOfWeekNumber New Measure 30/01/2023 0:00 2316 1650 2 2310 31/01/2023 0:00 2263 1654 3 2315 01/02/2023 0:00 2023 1606 4 2248 02/02/2023 0:00 2281 1587 5 2222 03/02/2023 0:00 2220 1586 6 2221 04/02/2023 0:00 174 1611 7 2221 05/02/2023 0:00 1611 1 2221 06/02/2023 0:00 2129 1584 2 2183 07/02/2023 0:00 2119 1564 3 2154
