Forum Discussion

psorel's avatar
psorel
Helper I
1 year ago
Solved

Dynamic average

Hi,

 

MEET_DATE       MONTH+1           MONTH+2     MONTH+3    MONTH+4  MONTH+5 ( in september)
2024-01-01   10121415 


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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      psorel
      Helper I
      1 year ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.