help in rolling average time intelligence

[Enrique_Pi1000]

Need some help/ I have build te following measure tu obtain the performance of cripto Ethereum:

RendimientoEtherum=

VAR Fechaini =

IF (

[Cot_Ethereum] > 0;

CALCULATE ( FIRSTDATE ( CRIPTOCOTIZ[FECHA] ); ALLSELECTED ( 'Calendario'[Date] ) );

BLANK ()

)

VAR Cotini =

CALCULATE (

[Cot_Ethereum];

Calendario[Date]= Fechaini;

ALLSELECTED ( 'Calendario'[Date])

)

RETURN

IF ( [Cot_Ethereum] > 0; DIVIDE ( [Cot_Ethereum]; Cotini; 0 ) - 1; BLANK () )

This can give me a chart o the performance and if filters o year apllied everythin is working good.

Furthermore I  have build another measur calculatin rollin avg in order to smooth the curve as this:

IF([Cot_Ethereum]>0;

CALCULATE(

AVERAGEX ( VALUES(Calendario[Date] ); [RendimientoEtherum] );ALLSELECTED(Calendario[Date]);

DATESINPERIOD(Calendario[Date];MAX(Calendario[Date]);-30;DAYS));

BLANK())

But when applied the year filter this secon meassure instead of starting at 0 performance fisrst day of the year , calculates de rolling average taking into account previous year data. How can I solve this? Tks . (allselected(calendario[date]) was introduce later but the result was the same as before without it

epinedoh@outlook.com

[123abc]

It seems like you are trying to calculate the rolling average of the Ethereum performance but are facing an issue with the year filter. To ensure that the rolling average starts at 0 performance on the first day of the year, you need to modify your rolling average calculation. You can achieve this by resetting the rolling average at the start of each year. Here's an adjusted version of your rolling average measure:

RollingAvgEthereum =
IF (
[Cot_Ethereum] > 0,
VAR MaxDate = MAX ( Calendario[Date] )
VAR MinDate = MIN ( Calendario[Date] )
VAR StartOfYear = CALCULATE ( MIN ( Calendario[Date] ), ALLSELECTED ( Calendario ) )
RETURN
IF (
[RendimientoEtherum] = BLANK (),
BLANK (),
IF (
MinDate = StartOfYear,
[RendimientoEtherum],
AVERAGEX (
FILTER (
ALLSELECTED ( Calendario ),
Calendario[Date] >= MaxDate - 30
&& Calendario[Date] <= MaxDate
&& Calendario[Date] >= StartOfYear
),
[RendimientoEtherum]
)
)
)
)

Here's how this measure works:

1. It first checks if the Ethereum performance is greater than 0.

2. It calculates the maximum and minimum dates in the selected date range.

3. It calculates the start date of the year for the selected year using CALCULATE and MIN.

4. If the minimum date is equal to the start of the year, it simply returns the Ethereum performance for that day.

5. If the minimum date is not the start of the year, it calculates the rolling average over the last 30 days of data within the selected year.

This should ensure that your rolling average starts at 0 performance on the first day of the year, and it considers the data within the selected year for the rolling average calculation.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Enrique_Pi1000]

Many thanks.. BUt that code provide me with the exact figure of [RendimientoEhterum] and only in the first day the figure is blank()

[123abc]

I see your concern, and it seems like you want the rolling average to start at zero on the first day of the year. To achieve this, you can slightly modify the RollingAvgPerformance measure to make sure the first day of the year shows 0 performance:

RollingAvgPerformance =
VAR CurrentDate = MAX ( Calendario[Date] )
VAR StartOfYear = DATE ( YEAR ( CurrentDate ), 1, 1 )
RETURN
IF (
[Cot_Ethereum] > 0,
IF (
CurrentDate = StartOfYear,
0, -- Set performance to 0 on the first day of the year
CALCULATE (
AVERAGEX ( VALUES ( Calendario[Date] ), [RendimientoEtherum] - 1 ),
FILTER (
ALLSELECTED ( Calendario ),
Calendario[Date] >= StartOfYear
&& Calendario[Date] <= CurrentDate
)
)
),
BLANK ()
)

In this modified measure:

1. If [Cot_Ethereum] is greater than 0, it first checks if CurrentDate is equal to the StartOfYear (the first day of the year). If it is, it returns 0 for performance on that day.

2. For all other days in the year, it calculates the rolling average as before.

This modification ensures that on the first day of the year, the performance is set to 0 while maintaining the rolling average behavior for subsequent days within the year.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.