Build average value of 30 min time interval

Question

Dear community,&nbsp;I have the following table existing of a column with time stamps (data type: Time, format hh:mm:ss) and the counted amount of sold goods (whole numbers). Example table:&nbsp;&nbsp;Time Stamp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sold Goods18:25:50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 107:21:05&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 311:45:35&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2....&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ...&nbsp;I now want to create a query which calculates the average amount of sold goods per 30 min interval.Output would then be:&nbsp;Time interval&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Average amount of sold goods07:00 - 07:30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 507:30 - 08:00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 308:00 - 08:30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; ....&nbsp;I tried with the following DAX code which should solve the problem:&nbsp;Averageby30min = &nbsp; &nbsp; VAR TimeIntervals =&nbsp; &nbsp; &nbsp; &nbsp; GENERATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE('TableName', 'TableName'[Time Stamp]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE('TableName', 'TableName'[Time Stamp]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@StartTime", TIME(HOUR('TableName'[Time Stamp]), FLOOR(MINUTE('TableName'[Time Stamp]) / 30) * 30, 0),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@EndTime", TIME(HOUR('TableName'[Time Stamp]), FLOOR(MINUTE('TableName'[Time Stamp]) / 30 + 1) * 30, 0)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(TimeIntervals, [@StartTime], [@EndTime]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM('TableName'[Time Stamp]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'TableName'[ime Stamp] &gt;= [@StartTime] &amp;&amp; 'Query1'[book_substring] &lt; [@EndTime]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp;&nbsp;Im always getting the following error code:Too few arguments were passed to the FLOOR function. The minimum argument count for the function is 2.&nbsp;Has anyone already solved this issue or know the error?&nbsp;Thank you very much.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

lbendlin · Answer

As the documentation to FLOOR states you must indicate what you want to round to.&nbsp; In your case should be 1.
&nbsp;
Usually these kinds of calendars should be created further upstream, in Power Query or in an external calendar table.

Forum Discussion

Build average value of 30 min time interval

1 Reply

Related content

Average Running total each quarter interval

Build 2.151.1182.0 Error

Average and Moving Average

Weekly Average

Accumulated average