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
jianlong's avatar
jianlong
Icon for Resolver I rankResolver I
4 years ago

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'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)) ))