Forum Discussion
Rolling 60 min count
I personally would rank the time (using rank_x) as rank_time, then countrows(fitler(data_schedule, time_rank<=60)). This is more straightforward and clean for me, but might be redundant for others.
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.
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?
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. The goal is to show multiple daily performance by day of week across multiple stations.
The expectation would be something akin to this:
but I am getting this:
I think I kind of know what you want.
rolling_select means you can define the rolling period: 5 means +/- 5mins
Here are the measures:
1, based on ranking: sometimes work great if it's too much trouble related to date or time.
Data_roll_60 =var x= if(isblank(SELECTEDVALUE(Rolling_selection[Rolling_select])),30,SELECTEDVALUE(Rolling_selection[Rolling_select])) // if blank, default to 30 minreturnCALCULATE(AVERAGE('Table'[Data]), filter(all('Table'),('Table'[rank]>=max('Table'[rank])-x)&& ('Table'[rank]<=max('Table'[rank])+x)))2, base on timeData_roll_x_min =var x = SELECTEDVALUE(Rolling_selection[Rolling_select])returncalculate(AVERAGE('Table'[Data]),FILTER(all('Table'),'Table'[Time]>=(max('Table'[Time])-time(0,x,0)) && 'Table'[Time]<= (max('Table'[Time])+ time(0,x,0)) ))