Rolling 60 min count

Question

I am struggling with this one and I would appreciate any help you can provide!

I am attempting to recreate an excel count using my unpivoted data so that it'll still interact with the 7 slicers on the page.

I have a line chart that shows the day from 0000-2359. The goal of this chart is to show a count of the data in a rolling (+/-30min) count. I have my key table that has each minute of the day mapped out without a date. I also have my time without date in my dataset. I would expect smooth peaks/valleys in the line chart, but for some reason all I can get is a discrete count.

I've tried using the below measure to calculate this, but that gives me the same result as just doing a calculate(count[Time_Station})). I am using my by min index as the Axis and the Rolling_60Min as my values, any thoughts on what I am doing wrong here?

Rolling_60Min =
//Calculates the Rolling 60 (+/- 30 min from the current time) for the filtered aggregates
CALCULATE(
COUNTROWS( Data_Schedule),
FILTER(
ALL( Data_Schedule[Time_Station]),
Data_Schedule[Time_Station] > MAX(Data_Schedule[Time_Station]) -30
&& Data_Schedule[Time_Station] <= MAX(Data_Schedule[Time_Station])
))

+

CALCULATE(
COUNTROWS( Data_Schedule),
FILTER(
ALL( Data_Schedule[Time_Station]),
Data_Schedule[Time_Station] < MAX(Data_Schedule[Time_Station]) +30
&& Data_Schedule[Time_Station] >= MAX(Data_Schedule[Time_Station])
))

jianlong · Answer

I personally would rank the time&nbsp; (using rank_x)&nbsp; as rank_time, then&nbsp; countrows(fitler(data_schedule, time_rank&lt;=60)).&nbsp; &nbsp;This is more straightforward and clean for me, but might be redundant for others.&nbsp;

dredre · Answer

I've never heard of rankx, but have been googling/watching some videos and I don't know that this would work for this problem. Are you saying to define a column in my dataset called rank_time, what data would be here? Then have a seperate measure? Would you mind clarifying? I'm new to that function so I might be misunderstanding.

jianlong · Answer

Could you post a file,, dataset or screenshot to elaborate and share what exactly you want, I might misunderstand what you need? Rolling data need to have a squence, which changes according to certain ranking, in your case, most recent 60 minutes; thus my expectation is that time is advancing. But in your case, it seems that start in 0:01 to 23:59? as you have remove the date?

dredre · Answer

My data has rows of individual entries that are unpivoted to allow for easier filtering. Each row has a datetime that I've split into 3 columns, Day of Week, Time and Date. Then I am using the below time buckets to show every min of the day as the axis. The data is not sorted in any particular order and there are multiple years of data.&nbsp; The goal is to show multiple daily performance by day of week across multiple stations.&nbsp;&nbsp;&nbsp;The expectation would be something akin to this:&nbsp;&nbsp;but I am getting this:&nbsp;&nbsp;

jianlong · Answer

I think I kind of know what you want.&nbsp; &nbsp;&nbsp;rolling_select means you can define the rolling period:&nbsp; 5 means +/- 5mins&nbsp;Here are the measures:1, based on ranking: sometimes work great if it's too much trouble related to date or time.&nbsp;Data_roll_60 = var x= if(isblank(SELECTEDVALUE(Rolling_selection[Rolling_select])),30,SELECTEDVALUE(Rolling_selection[Rolling_select]))&nbsp; &nbsp;// if blank, default to 30 minreturnCALCULATE(AVERAGE('Table'[Data]), filter(all('Table'),('Table'[rank]&gt;=max('Table'[rank])-x)&amp;&amp; ('Table'[rank]&lt;=max('Table'[rank])+x)))&nbsp;2, base on timeData_roll_x_min = var x = SELECTEDVALUE(Rolling_selection[Rolling_select])returncalculate(AVERAGE('Table'[Data]),FILTER(all('Table'),'Table'[Time]&gt;=(max('Table'[Time])-time(0,x,0)) &amp;&amp; 'Table'[Time]&lt;= (max('Table'[Time])+ time(0,x,0)) ))&nbsp;&nbsp;

Forum Discussion

Rolling 60 min count

5 Replies

Related content

Rolling accumulation

Rolling accumulation

Counting nulls

Count of rolling count

rolling count