Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Pawel-Widawski
Frequent Visitor

Calculate maximum aggregated value across time intervals

‎03-25-2022 10:14 AM

Hi,

 

I came across the following challange. I have the following table that contains only 3 columns.

PawelWidawski_3-1648228242611.png

 

It represents daily data (Date), 5 minutes interval time (Operation's Time Blocks) and the values that I am intrested in (processed stuff). What I want to calculate is the maximum value of processed stuff within 1 hour period.

So below is the graphic ilustration of data and I need to figure out between what time the number of processed stuff (sum) was the higest and what is it's value.

PawelWidawski_2-1648228099838.png

 

Looking at above chart it's probably time somwhere between 05:30 and 06:30 but what's the value of processed stuff and how to calculate it?

 

I hope no one will get as much headache on this as exercise as I did.. 🙂

 

 

Labels:
Message 1 of 5
414 Views
0
1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support
In response to Pawel-Widawski

‎03-28-2022 11:12 PM

Hi @Pawel-Widawski ,

 

I think you can try to create a calculated column to the max processed stuff in each 1 hour period.

MaxHour = 
CALCULATE (
    MAX ( 'Table'[processed stuff] ),
    FILTER (
        'Table',
        'Table'[Date] = EARLIER ( 'Table'[Date] )
            && HOUR ( 'Table'[Operation's Time Blocks] )
                = HOUR ( EARLIER ( 'Table'[Operation's Time Blocks] ) )
    )
)

Result is as below.

1.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Calculate maximum aggregated value across time intervals.pbix
Message 4 of 5
339 Views
0
4 REPLIES 4
Pawel-Widawski
Frequent Visitor

‎03-28-2022 01:32 PM

Thanks for advice, here is the sample table for this task.

 

DateOperation's Time Blocksprocessed stuff
01.03.202204:00:000
01.03.202204:05:000
01.03.202204:10:000
01.03.202204:15:000
01.03.202204:20:000
01.03.202204:25:009
01.03.202204:30:0095
01.03.202204:35:00244
01.03.202204:40:00335
01.03.202204:45:00583
01.03.202204:50:00745
01.03.202204:55:00879
01.03.202205:00:001034
01.03.202205:05:001180
01.03.202205:10:001347
01.03.202205:15:001526
01.03.202205:20:001759
01.03.202205:25:001933
01.03.202205:30:001912
01.03.202205:35:001864
01.03.202205:40:001904
01.03.202205:45:001743
01.03.202205:50:001769
01.03.202205:55:001778
01.03.202206:00:001754
01.03.202206:05:001695
01.03.202206:10:001638
01.03.202206:15:001557
01.03.202206:20:001357
01.03.202206:25:001249
01.03.202206:30:001228
01.03.202206:35:001222
01.03.202206:40:001191
01.03.202206:45:001224
01.03.202206:50:001127
01.03.202206:55:001082
01.03.202207:00:001047
01.03.202207:05:00988
01.03.202207:10:00881
01.03.202207:15:00784
01.03.202207:20:00753
01.03.202207:25:00677
01.03.202207:30:00634
01.03.202207:35:00539
01.03.202207:40:00439
01.03.202207:45:00383
01.03.202207:50:00400
01.03.202207:55:00393
01.03.202208:00:00397
01.03.202208:05:00410
01.03.202208:10:00514
01.03.202208:15:00557
01.03.202208:20:00578
01.03.202208:25:00621
01.03.202208:30:00647
01.03.202208:35:00699
01.03.202208:40:00754
01.03.202208:45:00769
01.03.202208:50:00695
01.03.202208:55:00719
01.03.202209:00:00873
01.03.202209:05:001036
01.03.202209:10:001013
01.03.202209:15:001004
01.03.202209:20:001098
01.03.202209:25:001219
01.03.202209:30:001405
01.03.202209:35:001552
01.03.202209:40:001677
01.03.202209:45:001757
01.03.202209:50:001822
01.03.202209:55:001758
01.03.202210:00:001576
01.03.202210:05:001465
01.03.202210:10:001418
01.03.202210:15:001462
01.03.202210:20:001458
01.03.202210:25:001403
01.03.202210:30:001210
01.03.202210:35:001014
01.03.202210:40:00845
01.03.202210:45:00691
01.03.202210:50:00592
01.03.202210:55:00560
01.03.202211:00:00500
01.03.202211:05:00406
01.03.202211:10:00370
01.03.202211:15:00323
01.03.202211:20:00272
01.03.202211:25:00209
01.03.202211:30:00262
01.03.202211:35:00322
01.03.202211:40:00314
01.03.202211:45:00353
01.03.202211:50:00434
01.03.202211:55:00460
01.03.202212:00:00447
01.03.202212:05:00447

 

Message 3 of 5
346 Views
0
v-rzhou-msft
Community Support
Community Support
In response to Pawel-Widawski

‎03-28-2022 11:12 PM

Hi @Pawel-Widawski ,

 

I think you can try to create a calculated column to the max processed stuff in each 1 hour period.

MaxHour = 
CALCULATE (
    MAX ( 'Table'[processed stuff] ),
    FILTER (
        'Table',
        'Table'[Date] = EARLIER ( 'Table'[Date] )
            && HOUR ( 'Table'[Operation's Time Blocks] )
                = HOUR ( EARLIER ( 'Table'[Operation's Time Blocks] ) )
    )
)

Result is as below.

1.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Calculate maximum aggregated value across time intervals.pbix
Message 4 of 5
340 Views
0
JulianaMacedo
Helper I
Helper I
In response to v-rzhou-msft

‎06-29-2022 06:01 AM

Hi!
I'm trying to do something similar using this solution but I'm getting an error:

Measure = 
CALCULATE(
    MAX(TimeDim[Minute]) - MIN(TimeDim[Minute]),
    MINX(
    FILTER(
        TimeDim,
        TimeDim[Hour] = Min(TimeDim[Hour])),
        TimeDim[Minute]
        )
)

 

Error: The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column. 

Any ideas what I'm doing wrong? I am trying to calculate the difference in time between the max time and the min in each hour interval. 

Message 5 of 5
294 Views
0
lbendlin
Super User
Super User

‎03-26-2022 11:28 AM

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.


https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Message 2 of 5
357 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All