Dynamic average

[psorel]

Hi,

MEET_DATE	MONTH+1	MONTH+2	MONTH+3	MONTH+4	MONTH+5 ( in september)
2024-01-01	10	12	14	15

Here is my dataset actually.
I want to calculte a dynamic average, i don't know how to organize my dataset. The user want so select "MONTH + 3" OR " Month+6" and see the dynamic average ( month1 + month2 + month3) / 3 if he selects MONTH+3, ( month1+...+month6) if he sleects month+6.
Moreover, new column can come with the same start " MONTH+", have you an idea ? 
Thank you

[Anonymous · Accepted solution]

Hi @psorel ,

For calculating a dynamic average, you can do the following. Hope it helps.

My sample data is as follows. Date is "MM/DD/YYY" format.

Create a calculated table to generates month numbers for adding.

Create a measure to calculate the dynamic average.

Average = 
var _sel=SELECTEDVALUE('Table 2'[Value])
var _start=MIN('Table'[Date])
var _end=EOMONTH(_start,_sel)
return 
DIVIDE(CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]>=_start&&[Date]<=_end)),_sel+1)

Here's the result. When I selected 2 in the slicer, the average is (1+2+3)/3=2 .

Best Regards,

Stephen Tao

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

[bhanu_gautam]

@psorel ,

Transform your dataset: Ensure your dataset is in a long format where each month is a separate row rather than a column. This will make it easier to perform dynamic calculations.

Create a parameter for user selection: Use a parameter to allow the user to select the number of months they want to include in the average calculation.

Create a measure for the dynamic average: Use DAX to create a measure that calculates the average based on the selected parameter.

DAX
DynamicAverage =
VAR SelectedMonths = SELECTEDVALUE('Parameter'[Parameter])
RETURN
CALCULATE(
AVERAGE('YourTable'[VALUE]),
FILTER(
'YourTable',
'YourTable'[MONTH] <= "MONTH+" & SelectedMonths
)
)

Add a slicer to your report for the parameter.
Use the DynamicAverage measure in your visuals to display the dynamic average based on user selection.

[psorel]

Thank you, if i change my dataset in lines, how can i have a table  visual with each Month in column? should i create one measure for each month?