Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DreDre
Helper II
Helper II

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

5 REPLIES 5
jianlong
Resolver I
Resolver I

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. 

 

DreDre_1-1646317840771.png

 

The expectation would be something akin to this:

DreDre_2-1646317960985.png

 

 

but I am getting this:

DreDre_3-1646318081137.png

 

 

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)) ))
 
jianlong_0-1646373749773.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors