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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
metalfortune
New Member

Countrows from the same data between time intervals

Hi every body,
Im trying to use measures with DAX to obtain the max number of travels(which are my rows) within 2 hours in my dataset that can also be interpreted as max busy vehicles
what i can think so far is the next:

Occupation = MAXX(
    GROUPBY(data,[Time],"Traffic",SUMX(
        CURRENTGROUP(),-(-(ALL(data[Time])<=[Time] && ALL(data[Time])>[Time]-1/12))
    )
),[Traffic])
However, the code is not working.
Thank you for advice!

Here a sample of my data:
Time
01/01/2024 12:25:00 AM
01/01/2024 12:25:00 AM
01/01/2024 12:25:00 AM
01/01/2024 12:25:00 AM
01/01/2024 12:25:00 AM
01/01/2024 12:25:00 AM
01/01/2024 12:25:00 AM
01/01/2024 12:50:00 AM
01/01/2024 03:00:00 AM
01/01/2024 05:45:00 AM
01/01/2024 05:45:00 AM
01/01/2024 05:45:00 AM
01/01/2024 06:00:00 AM
01/01/2024 06:00:00 AM
01/01/2024 06:00:00 AM
01/01/2024 06:00:00 AM
01/01/2024 09:00:00 AM
01/01/2024 12:00:00 PM
01/01/2024 12:00:00 PM
01/01/2024 12:00:00 PM
01/01/2024 12:40:00 PM
01/01/2024 02:00:00 PM
01/01/2024 03:30:00 PM
01/01/2024 07:00:00 PM
02/01/2024 12:50:00 AM
02/01/2024 01:00:00 AM
02/01/2024 06:20:00 AM
02/01/2024 08:50:00 AM
02/01/2024 08:50:00 AM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 12:25:00 PM
02/01/2024 06:15:00 PM
02/01/2024 06:15:00 PM
02/01/2024 06:15:00 PM
02/01/2024 06:15:00 PM
02/01/2024 06:15:00 PM
02/01/2024 06:15:00 PM
02/01/2024 06:15:00 PM
02/01/2024 10:00:00 PM
02/01/2024 10:00:00 PM
02/01/2024 11:00:00 PM
02/01/2024 11:30:00 PM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:25:00 AM
03/01/2024 12:50:00 AM
03/01/2024 01:00:00 AM
03/01/2024 06:20:00 AM
03/01/2024 08:45:00 AM
03/01/2024 08:45:00 AM
03/01/2024 08:45:00 AM
03/01/2024 08:45:00 AM
03/01/2024 08:45:00 AM
03/01/2024 10:30:00 AM
03/01/2024 06:15:00 PM
03/01/2024 06:15:00 PM
03/01/2024 06:15:00 PM
03/01/2024 06:15:00 PM
03/01/2024 06:15:00 PM
03/01/2024 06:15:00 PM
03/01/2024 06:15:00 PM
03/01/2024 06:15:00 PM
03/01/2024 06:15:00 PM
03/01/2024 06:15:00 PM
03/01/2024 10:30:00 PM
03/01/2024 10:30:00 PM
03/01/2024 10:30:00 PM
03/01/2024 11:00:00 PM
03/01/2024 11:00:00 PM
 
1 ACCEPTED SOLUTION

somehow bumped with what I wanted (I believe cant understand my own solution at all).


Here is the solution to the groupby but something weird is making my max here 26 instead of 25, i believe has to be with rounding 1/12 (the 2 hours) spoiling the the > and turning similar to >=,

metalfortune_1-1725405453918.png
then I have my full solution:

Occupation = MAXX(
    ADDCOLUMNS( GROUPBY(data,[Time]),
    "Busy",COUNTROWS(FILTER(data,data[Time]<=EARLIER([Time]) && data[Time]>EARLIER([Time])-1/12)))
,[Busy])
Compared to yours, individual times look the same but final numbers perform different:
metalfortune_2-1725405905680.png

 

Anyway, thanks for being my supplier @ahadkarimi, take care.

View solution in original post

2 REPLIES 2
ahadkarimi
Solution Specialist
Solution Specialist

Hi @metalfortune, give this a try, and if you encounter any issues, let me know.

 

Create a calculated column:

TimeBucket = INT(HOUR([Time])/2) + INT(MINUTE([Time])/120) + DATEVALUE([Time])

Then, create a measure:

MaxBusyVehicles = 
CALCULATE(
    MAXX(
        SUMMARIZE(
            data, 
            [TimeBucket], 
            "VehicleCount", COUNTROWS(data)
        ),
        [VehicleCount]
    )
)

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

somehow bumped with what I wanted (I believe cant understand my own solution at all).


Here is the solution to the groupby but something weird is making my max here 26 instead of 25, i believe has to be with rounding 1/12 (the 2 hours) spoiling the the > and turning similar to >=,

metalfortune_1-1725405453918.png
then I have my full solution:

Occupation = MAXX(
    ADDCOLUMNS( GROUPBY(data,[Time]),
    "Busy",COUNTROWS(FILTER(data,data[Time]<=EARLIER([Time]) && data[Time]>EARLIER([Time])-1/12)))
,[Busy])
Compared to yours, individual times look the same but final numbers perform different:
metalfortune_2-1725405905680.png

 

Anyway, thanks for being my supplier @ahadkarimi, take care.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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