Cumulative Rolling Average with multiple values on same date

[zenton]

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

[Anonymous · Accepted solution]

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.