Forum Discussion

zenton's avatar
zenton
Helper II
1 year ago
Solved

Cumulative Rolling Average with multiple values on same date

Hi,

I am having issues with rolling average measure.

The 3 values highlighted should be 179.59, 181.83 & 183.05 but they all calculate to 183.05.

 

Mean =
CALCULATE (
AVERAGE ( 'Labware'[Value] ),
DATESBETWEEN ( Labware[Calendar Date], [Historical Date], [First Sample Date] ),
ALL ( Labware[LIMS Text ID] )
)

 

Historical Date equals start date from looklup (29/11/2006) in this example)

First Sample Date =
CALCULATE ( MIN ( Labware[Calendar Date] ), ALLSELECTED ( '2DateTable'[Date] ) )

 

 

Thanks Rodney

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi zenton ,

     

    Based on your description I created simple data:

     

    You can try creating an index column:

     

    Try the following DAX:

    Mean = 
VAR _historyindex=MINX(ALL('Labware'),'Labware'[Index])
VAR _firstindex=MAX('Labware'[Index])
RETURN
CALCULATE(AVERAGE('Labware'[Value]),FILTER(ALL('Labware'),'Labware'[Index]>=_historyindex&&'Labware'[Index]<=_firstindex))

     

    Result:

    Best Regards,
    Zhu

     

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi zenton ,

     

    Based on your description I created simple data:

     

    You can try creating an index column:

     

    Try the following DAX:

    Mean = 
VAR _historyindex=MINX(ALL('Labware'),'Labware'[Index])
VAR _firstindex=MAX('Labware'[Index])
RETURN
CALCULATE(AVERAGE('Labware'[Value]),FILTER(ALL('Labware'),'Labware'[Index]>=_historyindex&&'Labware'[Index]<=_firstindex))

     

    Result:

    Best Regards,
    Zhu

     

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