Forum Discussion

DreDre's avatar
DreDre
Icon for Helper II rankHelper II
4 years ago

Rolling 60 min count

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 o...
Need Help
DreDre's avatar
DreDre
Icon for Helper II rankHelper II
4 years ago

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:

 

 

jianlong's avatar
jianlong
Icon for Resolver I rankResolver I
4 years ago

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 min
return
CALCULATE(AVERAGE('Table'[Data]), filter(all('Table'),('Table'[rank]>=max('Table'[rank])-x)&& ('Table'[rank]<=max('Table'[rank])+x)))
 
2, base on time
Data_roll_x_min =
var x = SELECTEDVALUE(Rolling_selection[Rolling_select])
return
calculate(AVERAGE('Table'[Data]),FILTER(all('Table'),'Table'[Time]>=(max('Table'[Time])-time(0,x,0)) && 'Table'[Time]<= (max('Table'[Time])+ time(0,x,0)) ))