Forum Discussion
Helper IDynamic average
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.
Super Userpsorel ,
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.
psorelThank 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?