Rolling 24-hour average with timestamp data

[Anonymous]

Hi all,

My data are approx as follows:

Dataset	         Obs Time	Obs Value
name of station	   dtg	         mcgm/m3

I require to create a 24-hour rolling average of Obs Value where Dataset stays same i.e. for each monitoring station (this is pollution data). My logic is thus:

Average of all Obs Values for a single Dataset in the 24 hours preceding the Obs time (to create an additional calculated column)

So far, I have tried multiple approaches, but cannot get my syntax right. DATEDIFF/DATEBETWEEN variants don't seem to work. I have created a fourth column called Obs Time-1 that subtracts 24 hours from Obs Time; thus what I want is the average for:

- ObsValues

- All matching Dataset

- All within Obs Time-1 to Obs Time.

My current DAX (note, I've tried dozens of variants) is as follows:

=CALCULATE(
AVERAGE(DATA_AIR_QUALITY[Value (Micrograms per Cubic Metres)]),
FILTER(DATA_AIR_QUALITY,
DATA_AIR_QUALITY[DataSet]=EARLIEST(DataSet]) // match the obs station
&& DATA_AIR_QUALITY[ObsTime]>EARLIEST(DATA_AIR_QUALITY[ObsTime-1]) // later than 24 hours previous
&& DATA_AIR_QUALITY[ObsTime]<EARLIEST(DATA_AIR_QUALITY[ObsTime]) // earlier than current stamp, but might be <=
)
)

[amitchandak]

@Anonymous , try like

New Column =
AVERAGEX(FILTER(DATA_AIR_QUALITY,
DATA_AIR_QUALITY[DataSet]=earlier(DataSet]) // match the obs station
&& DATA_AIR_QUALITY[ObsTime]>earlier(DATA_AIR_QUALITY[ObsTime])-1 // later than 24 hours previous
&& DATA_AIR_QUALITY[ObsTime]<earlier(DATA_AIR_QUALITY[ObsTime]) // earlier than current stamp, but might be <=
),DATA_AIR_QUALITY[Value (Micrograms per Cubic Metres)])

new Measure

=CALCULATE(
AVERAGE(DATA_AIR_QUALITY[Value (Micrograms per Cubic Metres)]),
FILTER(DATA_AIR_QUALITY,
DATA_AIR_QUALITY[DataSet]=max(DataSet]) // match the obs station
&& DATA_AIR_QUALITY[ObsTime]>=max(DATA_AIR_QUALITY[ObsTime])-1 // later than 24 hours previous
&& DATA_AIR_QUALITY[ObsTime]<max(DATA_AIR_QUALITY[ObsTime]) // earlier than current stamp, but might be <=
)
)

[Anonymous]

Hi,

The suggested fix didn't work. However a co-worker came up with one that did (not very elegant, but did the job!) Here it is:

= (CALCULATE(
SUM(DATA_AIR_QUALITY[Value (Micrograms per Cubic Metres)]),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[Date]=(EARLIEST(DATA_AIR_QUALITY[Date])-1)),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[DataSet]=EARLIEST(DATA_AIR_QUALITY[DataSet])),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[Time]>=EARLIEST(DATA_AIR_QUALITY[Time])))
+ CALCULATE(SUM(DATA_AIR_QUALITY[Value (Micrograms per Cubic Metres)]),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[Date]=EARLIEST(DATA_AIR_QUALITY[Date])),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[DataSet]=EARLIEST(DATA_AIR_QUALITY[DataSet])),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[Time]<EARLIEST(DATA_AIR_QUALITY[Time]))))/
(CALCULATE(COUNTROWS(DATA_AIR_QUALITY),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[Date]=(EARLIEST(DATA_AIR_QUALITY[Date])-1)),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[DataSet]=EARLIEST(DATA_AIR_QUALITY[DataSet])),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[Time]>=EARLIEST(DATA_AIR_QUALITY[Time])))
+ CALCULATE(COUNTROWS(DATA_AIR_QUALITY),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[Date]=EARLIEST(DATA_AIR_QUALITY[Date])),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[DataSet]=EARLIEST(DATA_AIR_QUALITY[DataSet])),
FILTER(DATA_AIR_QUALITY,DATA_AIR_QUALITY[Time]<EARLIEST(DATA_AIR_QUALITY[Time]))))